Add column names as rows

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sreesuku2
Participant
Posts: 45
Joined: Tue Oct 22, 2013 11:45 am

Add column names as rows

Post by sreesuku2 »

I have file in the below format

Code: Select all

ID	Sev 1 	Sev 2 	Sev 3
ABC	0.45	1	1
PQR	0.45	1	2
XYZ	0.45	1	1
I want to change this to the new format as below

Code: Select all

ID	Severity	Values
ABC	Sev1	0.45
ABC	Sev2	1
ABC	Sev3	1
PQR	Sev1	0.45
PQR	Sev2	1
PQR	Sev3	2
XYZ	Sev1	0.45
XYZ	Sev2	1
XYZ	Sev3	1
To bring the column names as rows and add the corresponding values.

Regards
Sree
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Upstream of the Pivot stage generate a delimited list of column names, and pivot on that also.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
roy
Participant
Posts: 2598
Joined: Wed Jul 30, 2003 2:05 am
Location: Israel

Post by roy »

Hi,
You can "simply" use the Hashed file pivot option.
Step 1 transform your records so:
ID column gets te ID value
Data column gets the string "Sev1|": <sev2 column data> : @VM "Sev2|": ... and so on
then when you read the Hashed file normalize on the data field and write to a seq fle with | as filed delimiter youll ge he file with wha you want
(After you set it in the column definition as a MV type column)

actually after achieving the desired format you can use the pivot stage to the same effect (if ou have a finite and constant number of fields.

IHTH ( I Hope This Helps),
Roy R.
Time is money but when you don't have money time is all you can afford.

Search before posting:)

Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Image
Post Reply