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 -- http://mail.python.org/mailman/listinfo/python-list
