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