how to generate SQL SELECT pivot table string

[John Machin]

McBooCzech wrote:
> 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
"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?