How to Convert Rows to Columns

[Cores]

CR 2008

Hi dear specialists, please help me with the idea how to CONVERT ROWS TO COLUMNS.

We have data in the following way:

User_ID Assignment Type
COTESYL EMSS Primary
COTESYL NSSS Secondary
COTESYL NCSS Secondary
COTESYL MANAGER Secondary
ELBAFA NSSS Primary
ELBAFA NCSS Secondary
ELBAFA MANAGER Secondary
PAGEAUL SERVICE Primary
PAGEAUL ACCT Secondary

How to display this data in the following way:

User_ID Primary Secondary Third Fourth

COTESYL EMSS NSSS NCSS MANAGER
ELBAFA NSSS NCSS Manager
PAGEAUL SERVICE ACCT

Each user in the d/base has a primary Assignment and zero, one or two secondary assignment. But in the report the users want to show a primary Assignment and then secondary assignments to show as secondary, third, fourth, etc.

Thank you very much for your time!

 

[CoSpringsGuy]

The subject name threw me off but based on what you want your output to look like this should work

Group report by field 1

create three formulas

//clear formula placed in group header
stringvar output;
output := "";
//accumulator formula placed in details section
stringvar output;
WhilePrintingRecords;
output := output & " " & {field2}
//output formula placed in group footer
{field1} & " " & {@accumulator formula}

hide group header
hide details


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[IanWaterman]

Minor addition to CoSpringsGuy solution, you need to add a sort on records to ensure primary first.

@Sort
If {AssignmentType} like 'Primary' then 1 else 2

Sort on this formula and second sort on {AssignmentType} to keep types together.

Ian

 

[Cores]

Thank you very much to both of you! Sorry, (It is my fault!)I did not mention that the users want a report in Excel.They want data for secondary assignments to be displayed in a separate column.

 

[Cores]

Thank you once again for your time.
I have accomplished the report by creating the query for users with secondary assignments (Converted rows to columns). Then joined it with the query for users with primary assignments.

The following is the query for converting the rows to columns:

SELECT usr_id,
MAX(sec1) sec1,
MAX(sec2) sec2,
MAX(sec3) sec3
FROM(SELECT -- Create 3 fields - 1 field for each possible rownumber
usr_id,
DECODE(MAX(rownumber),1,assign_sc,NULL) sec1,
DECODE(MAX(rownumber),2,assign_sc,NULL) sec2,
DECODE(MAX(rownumber),3,assign_sc,NULL) sec3
FROM(SELECT -- Add a row-number field for each assign_sc within an usr_id
assign_sc,
usr_id,
ROW_NUMBER() OVER(PARTITION BY usr_id ORDER BY assign_sc) rownumber
FROM
(
My Query for Secondary assignments

)
)
GROUP BY usr_id,assign_sc
)
GROUP BY usr_id
;