how to transpose a straight table

[Anonymous]

hello there,

i have a straight table looking like below:

group	count
A	132
A	156
A	78
B	24
B	66
C	89
C	109
C	172
C	201
C	33
C	27
D	49
D	55
D	61
D	18

I want to make it look like this:

group	count1	count2	count3	count4	count5	count6
A	132	156	78
B	24	66
C	89	109	172	201	33	27
D	49	55	61	18

CAN anyone help? is there a easy way?

thank you!

[Gysbert_Wassenaar · Accepted solution]

You'll have to transform the data in the script for that. I recommend you don't try to use a straight table, but a pivot table.

LOAD

     group,

     count,

     'count' & autonumber(count, group) as newfield

FROM

     ...source data...

     ;

Once you've transformed the data like above you can use group and newfield as dimensions in a pivot table and sum(count) as expression. In the pivot table drag the field newfield to the right above the expression so it gets pivoted to columns.

[Anonymous]

Hi Gysbert,

Thanks a lot! i tried it and it worked!

thanks again!