Hello all.
I searched forum for info on pivoting rows into columns (or flattening) and found very usefull link:
http://www.artfulsoftware.com/infotree/queries.php?&bw=1344#523
section 'Automate pivot table queries'.
It helps a lot but I need to take it a step further - I need to generate complete query for pivoting the data in my table.
I have table T1:
Measures Product value
'dollar_sales' 'product1' 15
'avg_sales' 'product1 20
'Base_Price' 'product1 2
'dollar_sales' 'product2 25
I need to create table T2
Product Dollar_Sales Avg_Sales Base_Price
'product1' 15 20 2
'product2' 25
In order to accomplish that I need to generate a query:
select Product,
sum(case when Measure = 'Dollar_Sales' then value end) as Dollar_Sales
,sum(case when Measure = 'Base_Price' then value end) as Base_Price
from T1
group by Product.
Link mentioned above provides elegant solution for generating pivoting expressions.
I am having trouble create complete query since I can't just concatenate first part 'select Product' with the list of pivoting expressions since they are generated dynamically by another sql statement.
Please help!
Thanks a lot in advance!
Pit