Page 1 of 1
how to convert a single row into multiple rows
Posted: Tue Oct 23, 2007 11:37 am
by mallikarjuna36
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
Posted: Tue Oct 23, 2007 12:55 pm
by roy
As said before Pivot Stage
Posted: Tue Oct 23, 2007 1:18 pm
by mallikarjuna36
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.
Posted: Tue Oct 23, 2007 3:24 pm
by ray.wurlod
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.
Posted: Wed Oct 24, 2007 3:00 am
by info_ds
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.
Posted: Wed Oct 24, 2007 11:04 am
by Ronetlds
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.
Posted: Wed Oct 24, 2007 7:54 pm
by shepli
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
Posted: Tue Oct 30, 2007 9:48 am
by rsaliah
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)
Posted: Wed Sep 23, 2009 9:42 am
by DSbox61
that was a very good solution from
info_ds. Just wanted to express my appreciation.
![Smile :)](./images/smilies/icon_smile.gif)