Pivot N colums one row

[mathias_schnoor]

Hi!

I have an input file (csv) or table with N columns.

The columns has maybe the syntax:

sequence_id;column1;column2;...column<N>


For example:

1;aText;aNumber;anOtherText;anOtherNumber

1;aText;aNumber;anOtherText;anOtherNumber

1;aText;aNumber;anOtherText;anOtherNumber

1;aText;aNumber;anOtherText;anOtherNumber

2;aText;aNumber;anOtherText;anOtherNumber

2;aText;aNumber;anOtherText;anOtherNumber

3;aText;aNumber;anOtherText;anOtherNumber

3;aText;aNumber;anOtherText;anOtherNumber

3;aText;aNumber;anOtherText;anOtherNumber

... and so on.

By the first step I will concate the necessary column entries to a new column.

At the second step I will pivot the new columns to one row.

Because from my input data I never knew before how many rows I get and how many rows has the same sequence id.

I must dynamicly pivot the concate columns.

I use the Cross Tab Tool

and get for each sequence_id a column with the concate column entries.

But get a many column entries with no content.

How  is it possible to get only two columns?

One column with the sequence_id and one column with includes the content of column 1,2,3,4,..,N

For each sequence_id get a suitable content of the column.

For my example

sequence_id;content_column

1;&start content one

2; &start content two

3;&start content three

4;...

5;...

..;...

Best regards

Mathias

[jdunkerley79 · Accepted solution]

Add a formula tool creating a new value:

In your Cross Tab select this as the header field.

Keep the grouping as you had it and the result should be what you want.

[mathias_schnoor]

Thank you for your support.

That is what I mean!