Transpose Query Data

[blaubner]

Imagine you create a query (Get_Majors) that collects the ID and major(s) for three students. The query results are below:

ID	Major
01938	MATH
01938	PHYS
01938	CSCI
32211	HIST
29403	PSYC

I want to transpose the data on MAJOR by ID. The end result would look like this:

ID	Major1	Major2	Major3
01938	MATH	PHYS	CSCI
32211	HIST
29403	PSYC

Super easy in SAS. Hoping its super easy in report studio. I want to be able to build the query Get_Majors to look like the end result. I will be left joining other data onto Get_Majors once I get one row per student.

Thanks for your help. I apologize if this has already been asked elsewhere. I could not find an example thread when I searched.

Brendan

[bdbits]

You've not said whether your data source is relational or dimensional, but this looks like a basic crosstab to me.

Have you perused the Report Studio user guide (same as help)? It is actually pretty good as a tutorial / introduction on Cognos essentials.

[blaubner]

ID and Major sit in a table, so that would mean it's relational data right?

Are you suggesting I build a cross tab on a report page? How would I get the report page into my list of queries under Query Explorer so I could left join more data onto the cross tab?

Brendan

[New_Guy]

Hi,
How are you determining which major is major 1 or 2 or 3? If you have to show it in the way you want to then use a list and add 1st column as ID and second column a crosstab and place the major code in the column and format the crosstab to hide rows and fact cells and the corner.
Good luck
New guy