I want to Pivot the data which in one column

[ravi468]

Hi,

I need to pivot the data which is in a table column in the below way:

INPUT

Name Account_No state

Ram 123 CA,NY,MS
Pam 857 IL,KS

OUTPUT

Name Account_No state

Ram 123 CA
Ram 123 NY
Ram 123 MS
Pam 857 IL
Pam 857 KS


Can please some one help me how to achive the output.

Thanks in advance.

[chulett]

You'll need to split those values out into individual columns and then pivot.

[ravi468]

Thanks Criag,

For each person there may be more than 50 states and one more thing is we get the data in a fixed witdh file and the data for the state field comes with a comma seperation, So I have loaded the data into a table in the above menctioned way, from there I am trying to pivot for the state.

Please let me know if there is any other way.

Thanks

[chulett]

Set up 50 new columns and use Field() in the derivation of each with the appropriate field number. Some will be null, filter them out post-pivot.

[ray.wurlod]

At least 50 columns. As many as - or more than - the number of distinct values in the STATE column. Filter out nulls or empty values after the Pivot has occurred.