how to generate SQL SELECT pivot table string

[Michael Spencer]

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?
> 
> Thanks
> 
> Petr McBooCzech
> 
 >>> step1result = """2000
... 2001
... 2002
... 2003""".splitlines()
 >>> step1result
['2000', '2001', '2002', '2003']
 >>> step2query = "Prefix " + ",".join(["Case %s" % year for year in 
step1result]) + " Postfix"
 >>> step2query
'Prefix Case 2000,Case 2001,Case 2002,Case 2003 Postfix'


HTH
Michael