PIVOT in framework manager

[saumil287]

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   

[blom0344]

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

[saumil287]

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

[blom0344]

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)

[saumil287]

ok
Thanks for your reply.