how to convert a single row into multiple rows
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 12
- Joined: Mon Oct 22, 2007 7:58 am
how to convert a single row into multiple rows
I am having a job scenarion as below.
The values are reading from tables and values as follows.
id | Address
1|abc,sbc,sge
2|dgw,hgt,hge
i want the output as below
1|abc
1|sbc
1|sge
2|dgw
2|hgt
2|hge
Could any of you guys help me on this issue.
But in the input of second coulmn there is no consistency of data,i mean
1|abc,sgd
2|abc
Consistency will not be there for 2 nd column.
Thanks for your time and effort.
Thanks,
Arjun
The values are reading from tables and values as follows.
id | Address
1|abc,sbc,sge
2|dgw,hgt,hge
i want the output as below
1|abc
1|sbc
1|sge
2|dgw
2|hgt
2|hge
Could any of you guys help me on this issue.
But in the input of second coulmn there is no consistency of data,i mean
1|abc,sgd
2|abc
Consistency will not be there for 2 nd column.
Thanks for your time and effort.
Thanks,
Arjun
As said before Pivot Stage
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](http://www.worldcommunitygrid.org/images/logo.gif)
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](http://www.worldcommunitygrid.org/images/logo.gif)
-
- Premium Member
- Posts: 12
- Joined: Mon Oct 22, 2007 7:58 am
Could you please explain me on this.How to split the data using of pivot data.Because as of i know,we can split the data for following scenario.
id|sales1|sales2
and we can convert as
id |sales1
id|sales2
But my scenario is different.
My scenario is
id|sales1,sales2
i want to split the data as
id|sales1
id|sales2
please help me.
Thanks in advance.
id|sales1|sales2
and we can convert as
id |sales1
id|sales2
But my scenario is different.
My scenario is
id|sales1,sales2
i want to split the data as
id|sales1
id|sales2
please help me.
Thanks in advance.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need to parse the comma-delimited field into separate columns, either when reading the file or using Field() functions in a Transformer stage upstream of the Pivot stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi Arjun,
Try this....
1)Read your data in a sequential as a single column(c1) with demiliter specified as 000
2)create a stage variable svPivot and use this code
Ereplace(DSLink2.c1,",",char(10):char(13):field(DSLink2.c1,"|",1):"|") -->svPivot
3)write the stage variable to the output sequential file with delimiter again marked as 000
hope there may be better way of doing it.
Good luck.
Try this....
1)Read your data in a sequential as a single column(c1) with demiliter specified as 000
2)create a stage variable svPivot and use this code
Ereplace(DSLink2.c1,",",char(10):char(13):field(DSLink2.c1,"|",1):"|") -->svPivot
3)write the stage variable to the output sequential file with delimiter again marked as 000
hope there may be better way of doing it.
Good luck.
create 2 output links from transformer. keep two rec counter stage variables, rec ctr, rec ctr + 1 to be used for sorting after transformer. Increment both rec ctrs by 2 for each input rec.
for inrec 1 ->
out link1 rec ctr, inrec1 data1
out link2 rec ctr + 1, inrec1 data2
sort on rec ctr field after transformer, then merge.
Or just use pivot stage.
for inrec 1 ->
out link1 rec ctr, inrec1 data1
out link2 rec ctr + 1, inrec1 data2
sort on rec ctr field after transformer, then merge.
Or just use pivot stage.
Hi Arjun,
You can also use a Link_Collector to do the job. In this case, you need to parse the comma-delimited field (e.g. into val1, val2, val3) using Field() functions.
Send all (full) records into 3 transformers. Each transformer outputs ID plus one of the parsed value (val1 - val3). Link the output to a Link_Collector, and the output from the Link_Collector should be what you needed. Remember, the column names of all the 3 transformers should be the same.
Hope this helps. Thanks,
shepli
You can also use a Link_Collector to do the job. In this case, you need to parse the comma-delimited field (e.g. into val1, val2, val3) using Field() functions.
Send all (full) records into 3 transformers. Each transformer outputs ID plus one of the parsed value (val1 - val3). Link the output to a Link_Collector, and the output from the Link_Collector should be what you needed. Remember, the column names of all the 3 transformers should be the same.
Hope this helps. Thanks,
shepli
Dude,
You can pass your two columns through a Transform stage to create a multivalued column of the address field. This you can do by replacing the comma ',' with a @FM ( change(Address,',',@FM) ). The output of this should then go to a hash file. When you output the hash file just define the addess column as type multivalued and 'normalise' the output and let DataStage do the rest.![Wink :wink:](./images/smilies/icon_wink.gif)
You can pass your two columns through a Transform stage to create a multivalued column of the address field. This you can do by replacing the comma ',' with a @FM ( change(Address,',',@FM) ). The output of this should then go to a hash file. When you output the hash file just define the addess column as type multivalued and 'normalise' the output and let DataStage do the rest.
![Wink :wink:](./images/smilies/icon_wink.gif)