how to generate SQL SELECT pivot table string

M

McBooCzech

Hallo all,

I am trying to generate SQL SELECT command which will return pivot
table. The number of column in the pivot table depends on the data
stored in the database. It means I do not know in advance how many
columns the pivot table will have.

For example I will test the database as following:
SELECT DISTINCT T1.YEAR FROM T1

The SELECT command will return:
2002
2003
2004
2005

So I would like to construct following select:

select T1.WEEK,
SUM (case T1.YEAR when '2002' then T1.PRICE else 0 END) Y_02,
SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
SUM (case T1.YEAR when '2005' then T1.PRICE else 0 END) Y_05
from T1
group by T1.week

which will return pivot table with 5 columns:
WEEK, Y_02, Y_03, Y_04, Y_05,

but if the command "SELECT DISTINCT T1.YEAR FROM T1" returns:
2003
2004

I have to construct only following string:

select T1.WEEK,
SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
from T1
group by T1.week

which will return pivot table with 3 columns:
WEEK, Y_03, Y_04

Can anyone help and give me a hand or just direct me, how to write a
code which will generate SELECT string depending on the data stored in
the database as I described?

Thanks

Petr McBooCzech
 
M

mensanator

McBooCzech said:
Hallo all,

I am trying to generate SQL SELECT command which will return pivot
table. The number of column in the pivot table depends on the data
stored in the database. It means I do not know in advance how many
columns the pivot table will have.

For example I will test the database as following:
SELECT DISTINCT T1.YEAR FROM T1

The SELECT command will return:
2002
2003
2004
2005

So I would like to construct following select:

select T1.WEEK,
SUM (case T1.YEAR when '2002' then T1.PRICE else 0 END) Y_02,
SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
SUM (case T1.YEAR when '2005' then T1.PRICE else 0 END) Y_05
from T1
group by T1.week

which will return pivot table with 5 columns:
WEEK, Y_02, Y_03, Y_04, Y_05,

but if the command "SELECT DISTINCT T1.YEAR FROM T1" returns:
2003
2004

I have to construct only following string:

select T1.WEEK,
SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
from T1
group by T1.week

which will return pivot table with 3 columns:
WEEK, Y_03, Y_04

Can anyone help and give me a hand or just direct me, how to write a
code which will generate SELECT string depending on the data stored in
the database as I described?

Thanks

Petr McBooCzech
Click to expand...

In MS-Access, the appropriate SQL statement is

TRANSFORM Sum(T1.price) AS SumOfprice
SELECT T1.week
FROM T1
GROUP BY T1.week
PIVOT T1.year;

Note the keywords TRANSFORM and PIVOT. These change an
ordinary SELECT query into what Access calls a CROSSTAB
query. In such a query, the number of columns is created
automatically (subject to the limitation of a maximum of
256 columns).

For example, if your T1 table contains:

year week price
2002 1 123
2002 10 456
2002 20 254
2002 30 253
2002 40 325
2002 50 111
2003 1 254
2003 10 256
2003 20 854
2003 30 125
2003 40 845
2003 50 562
2004 1 425
2004 10 123
2004 20 212
2004 30 555
2004 40 412
2004 50 852


The query shown will output:

week 2002 2003 2004
1 123 254 425
10 456 256 123
20 254 854 212
30 253 125 555
40 325 845 412
50 111 562 852


Now, if you add another year's worth of data,

year week price
2005 1 666
2005 10 555
2005 20 444
2005 30 333
2005 40 222
2005 50 111

the query (without any modiification) will now output

week 2002 2003 2004 2005
1 123 254 425 666
10 456 256 123 555
20 254 854 212 444
30 253 125 555 333
40 325 845 412 222
50 111 562 852 111


If you had multiple records for each year/week you would, of course,
see the aggregate result (in this case, Sum). Watch what happens
when I duplicate the 2005 records:

week 2002 2003 2004 2005
1 123 254 425 1332
10 456 256 123 1110
20 254 854 212 888
30 253 125 555 666
40 325 845 412 444
50 111 562 852 222
 
M

Michael Spencer

McBooCzech said:
Hallo all,

I am trying to generate SQL SELECT command which will return pivot
table. The number of column in the pivot table depends on the data
stored in the database. It means I do not know in advance how many
columns the pivot table will have.

For example I will test the database as following:
SELECT DISTINCT T1.YEAR FROM T1

The SELECT command will return:
2002
2003
2004
2005

So I would like to construct following select:

select T1.WEEK,
SUM (case T1.YEAR when '2002' then T1.PRICE else 0 END) Y_02,
SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
SUM (case T1.YEAR when '2005' then T1.PRICE else 0 END) Y_05
from T1
group by T1.week

which will return pivot table with 5 columns:
WEEK, Y_02, Y_03, Y_04, Y_05,

but if the command "SELECT DISTINCT T1.YEAR FROM T1" returns:
2003
2004

I have to construct only following string:

select T1.WEEK,
SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
from T1
group by T1.week

which will return pivot table with 3 columns:
WEEK, Y_03, Y_04

Can anyone help and give me a hand or just direct me, how to write a
code which will generate SELECT string depending on the data stored in
the database as I described?

Thanks

Petr McBooCzech
Click to expand...
.... 2001
.... 2002
.... 2003""".splitlines()
>>> step1result ['2000', '2001', '2002', '2003']
>>> step2query = "Prefix " + ",".join(["Case %s" % year for year in step1result]) + " Postfix"
>>> step2query
Click to expand...
Click to expand...
Click to expand...
'Prefix Case 2000,Case 2001,Case 2002,Case 2003 Postfix'


HTH
Michael
 
M

McBooCzech

Thanks for your comment but I am NOT looking for the answer to the
question: "Which SQL command will return requested pivot table"(anyway
it will be an OFF TOPIC question here). My SQL SELECT statement works
fine with Firebird 1.5! What I am looking how to generate this SELECT
using Python. Anyway thanks for tyring to help :)
 
J

John Machin

McBooCzech said:
Hallo all,

I am trying to generate SQL SELECT command which will return pivot
table. The number of column in the pivot table depends on the data
stored in the database. It means I do not know in advance how many
columns the pivot table will have.

For example I will test the database as following:
SELECT DISTINCT T1.YEAR FROM T1

The SELECT command will return:
2002
2003
2004
2005

So I would like to construct following select:

select T1.WEEK,
SUM (case T1.YEAR when '2002' then T1.PRICE else 0 END) Y_02,
SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
SUM (case T1.YEAR when '2005' then T1.PRICE else 0 END) Y_05
from T1
group by T1.week

which will return pivot table with 5 columns:
WEEK, Y_02, Y_03, Y_04, Y_05,

but if the command "SELECT DISTINCT T1.YEAR FROM T1" returns:
2003
2004

I have to construct only following string:

select T1.WEEK,
SUM (case T1.YEAR when '2003' then T1.PRICE else 0 END) Y_03,
SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04,
from T1
group by T1.week

which will return pivot table with 3 columns:
WEEK, Y_03, Y_04

Can anyone help and give me a hand or just direct me, how to write a
code which will generate SELECT string depending on the data stored in
the database as I described?
step1result = ["2003", "2004"] # for example
prologue = "select T1.WEEK, "
template = "SUM (case T1.YEAR when '%s' then T1.PRICE else 0 END) Y_%s"
epilogue = " from T1 group by T1.week"
step2sql = prologue + ", ".join([template % (x, x[-2:]) for x in step1result]) + epilogue
step2sql
Click to expand...
Click to expand...
Click to expand...
"select T1.WEEK, SUM (case T1.YEAR when '2003' then T1.PRICE else 0
END) Y_03, SUM (case T1.YEAR when '2004' then T1.PRICE else 0 END) Y_04
from T1 group by T1.week"
Of course you may need to adjust the strings above to allow for your
local SQL syntax (line breaks, line continuations, semicolon at the end
maybe, ...).

A few quick silly questions:
Have you read the Python tutorial?
Do you read this newsgroup (other than answers to your own questions)?
Could you have done this yourself in a language other than Python?
 
M

McBooCzech

Tahnks a lot. Michalels sequence works flawlessly. And it is really
elegant :)

John said:
A few quick silly questions:
Have you read the Python tutorial?
Do you read this newsgroup (other than answers to your own questions)?
Could you have done this yourself in a language other than Python?
Click to expand...
Of course I have read (or still reading) Python tuorial and newsgroup
(No kidding:). And I am able to done this in VBA for example. But I am
not able to write such a elegant construction. Tanks a lot.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to add dropdown selected data to table using jquery 2
Only one table shows up with the information 2
can't generate iterator from list 3
How to use multiple instances of the same COM object at the sametime 0
Collect Excel Data from Website 5
Adding 'download' column to existing 'visitors' table (as requested) 18
How to keep count of right answer and wrong answers in C++? 0
Fastest way to store ints and floats on disk 2

Members online

No members online now.
Total: 35 (members: 1, guests: 34)
Robots: 455

Forum statistics

Threads
473,769
Messages
2,569,580
Members
45,055
Latest member
SlimSparkKetoACVReview

Latest Threads

Top