Hi All,
I am having a QS imported from excel.
The Query Items are BU Area Customer Product Period revenue.
DIC AHD Polymers DOX 201101 5000
DIC PNR Polymers DOM 201002 4000
I want the period values to be as columns ie
BU Area Customer Product 201101 201002
DIC AHD Polymers DOX 5000 4000
DIC PNR Polymers DOM
I the same thing possible in Framework manager.
Your guidance will be appreciated.
Thanks in advance
Possible, but only if you know the number of required columns upfront. It is not comparable with the T-sql PIVOT structure.
It all comes down to a very extensive case script:
SELECT BU,AREA,CUSTOMER,PRODUCT,
SUM(CASE WHEN PERIOD = 201101 THEN REVENUE ELSE 0 END) AS M201101,
SUM(CASE WHEN PERIOD = 201102 THEN REVENUE ELSE 0 END) AS M201102,
SUM(CASE WHEN PERIOD = 201103 THEN REVENUE ELSE 0 END) AS M201103,
SUM(CASE WHEN PERIOD = 201104 THEN REVENUE ELSE 0 END) AS M201104,
.....
FROM SOMETABLE
GROUP BY BU,AREA,CUSTOMER,PRODUCT
Hi BLOM,
Thanks for your reply.
What I did was ,I wrote the pivot stmt in ms sql server 2005 and I copy the same pivot stmt into FM as query when right click on namespace and choose create new Querysubject.
There is no direct option to pivot the same.
Is there any Dynamically instead of hardcoding the period values.
I wanted to know whether the same can be achieved dynamically in FM instead of going to ms sql server and dumping the pivot stmt in FM.
Thanks again
As I wrote there is no equivalent to the PIVOT structure in SQL. You have to map model query items to the query output structure (number, name and type of column)
ok
Thanks for your reply.