how to convert a single row into multiple rows

[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

[roy]

As said before Pivot Stage

[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.

[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.

[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.

[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.

[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

[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.

[DSbox61]

that was a very good solution from info_ds. Just wanted to express my appreciation.