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