COGNOiSe.com - The IBM Cognos Community

ETL, App Development and other tools => COGNOS DecisionStream/Data Manager => Topic started by: vijayhai on 31 Jan 2014 10:14:49 AM

Title: Converting rows to columns
Post by: vijayhai on 31 Jan 2014 10:14:49 AM
Hi,

I have a question with regards to converting rows to columns and wanted to see if there are any readily available solution in data manager or if anyone has designed any solution in DM.

Below is the example of what I'm trying to accomplish.

Source :

Key   ID   Value
A   1   10
A   2   20
A   3   30
A   4   40
B   1   50
B   2   60

Target :

Key   Col1 Col2 Col3   Col4
A   10   20   30   40
B   50   60      

Please let me know your thoughts.

Thanks
VBS
Title: Re: Converting rows to columns
Post by: MFGF on 03 Feb 2014 07:51:13 AM
Yup. The old Reverse Pivoting technique. Hans Peeters wrote an article about this a long (long) time ago. The article used techniques we used to teach in the PowerHouse courses decades ago. When I taught Data Manager classes I used to hand out copies of this document when we covered the pivoting module - people would ask about it on a regular basis so I decided to pre-empt the question :) I haven't updated it since Data Manager 8, but the techniques still work as-is in 10.

You can find the document here (https://drive.google.com/file/d/0B6m-USVf1p1nclZLVjNRQWFRSS1QX2c2TlFBZjZwUQ/edit?usp=sharing).

Cheers!

MF.
Title: Re: Converting rows to columns
Post by: vijayhai on 03 Feb 2014 09:34:34 AM
Thanks Much MF,

This is great. I just tried the second solution as I needed a very quick one and it works great. I will try the first solution too.

Thanks
VBS