Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
sql in excel
#1
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
[...]
#2
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]
#3
and using only QM or other way?
#4
Lucas Wrote:and using only QM or other way?
:?:
#5
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?
#6
You probably know SQL better than I.
I think it will not work with Excel SQL.
#7
Do you know any link with all Excel SQL functions that will work with QM?
#8
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.
#9
It works using one sql query: "TRANSFORM SUM(b) SELECT a FROM [test$] GROUP BY a PIVOT FORMAT(c,'Short Date')"


Forum Jump:


Users browsing this thread: 1 Guest(s)