sql in excel

[Lucas]

I have a excel sheet with 3 columns(a, b, c):

a1 10 01/05/11
a2 20 02/05/11
a3 15 03/05/11
a1 12 02/05/11
a3 11 01/05/11
a1 13 02/05/11
[...]

how can I get something like this using sql?

___01 02 03 [...]
a1 10 25 00
a2 00 20 00
a3 11 00 15
[...]

[Gintaras]

I only can give example of using SQL with Excel.

Macro Database Help

Code:

Copy      Help

;Excel. As table name, use [worksheet name$]. First row is used for headers.
Database db2.Open(db2.CsExcel("$personal$\book1.xls"))
ARRAY(str) a2; int c2
db2.QueryArr("SELECT * FROM [Sheet1$] WHERE Country='USA'" a2 1)
for c2 0 a2.len
,out a2[c2]

[Lucas]

and using only QM or other way?

[Gintaras]

and using only QM or other way?

:?:

[Lucas]

I tried a code like this:

Macro Macro5

Code:

Copy      Help

str start_date("01/05/11") end_date("31/12/11") temp_date

temp_date=start_date

ExcelSheet es.Init("" 8|16)
Database db2.Open(db2.CsExcel(artemp))
ARRAY(str) a1
DATE d=start_date
int row=1
for r 0 a.len
,rep
,,q1.format("SELECT SUM(b) FROM [test$] WHERE DateValue(c)=#%s# AND a='%s'" temp_date a[r])
,,db2.QueryArr(q1 a1 1)
,,str x=a1
,,es.SetCell(x 1+r row)
,,d=d+1
,,temp_date=d
,,if(temp_date=end_date)
,,,row+1
,,,break

but many querys and takes time.

Is possible to use something like this: http://stackoverflow.com/questions/3946 ... en-2-dates to speed up the code?

[Gintaras]

You probably know SQL better than I.
I think it will not work with Excel SQL.

[Lucas]

Do you know any link with all Excel SQL functions that will work with QM?

[Gintaras]

I think, all Excel SQL functions work in QM.
When I need Excel SQL reference, I look in Access Help, but maybe you can find a better reference somewhere.

[Lucas]

It works using one sql query: "TRANSFORM SUM(b) SELECT a FROM [test$] GROUP BY a PIVOT FORMAT(c,'Short Date')"