how can we split into multiple records

[tostay2003]

Hi all,

need a bit of help with splitting into multiple records.

i have 7 columns as shown below (ps there are more than 7 columns in the table)

id,sales_typ1,sales_amt1,sales_typ2,sales_amt2, sales_typ3,sales_amt3.

I want to split into three records as below

id,sales_typ1,sales_amt1,
id, sales_typ2,sales_amt2,
id, sales_typ3,sales_amt3.

How can I do this in datastage?

Regards

[DSguru2B]

if your target meta data remains the same (3 columns) then you can split it in a tranformer and then collect it later on

[dwandbi]

Pivot stage and split the whole column into 3 cols

I never tried. Let me know if it gets results

thanks,
Tom

[tostay2003]

sorry posted a bit wrong

its actually

id, sales_typ1_amnt, sales_type2_amt, sales_type3_amt

and need it like

id, sales_typ1_amnt,
id sales_type2_amt,
id sales_type3_amt

[DSguru2B]

i have never used the pivot stage, so dont know weather it can be achieved via pivot stage.
A simpler solution is as i suggested, provied the target meta data remains identical,
pass your file through the transformer, have three links coming out of it
pass id to all three, and the second column to the first link, the third column to the second link and the last column to the third link. Collect it via link collector. Your final target, collected file will have the result you desire.

[DSguru2B]

If you have the option to use Enterprise Edition, then I would recommed you to use that and use the plug-in stage known as the PIVOT STAGE.
This stage will result in a definite solution to your problem.
Where as in the server this can be done through a transformer as mentioned above.
The limitation would be that your positions for the pivot should be static.

[ogmios]

Use the database Luke. Do something as follows in a DB stage:

Code: Select all

select ID, sales_typ1_amnt
from yourtable
union
select ID, sales_typ2_amnt
from yourtable
union
select ID, sales_typ3_amnt
from yourtable

Ogmios

[ogmios]

Moreover :D ... if you want to keep the amnt's in order, something as:

Code: Select all

select ID, sales_typ1_amnt, '1' as order
from yourtable
union
select ID, sales_typ2_amnt, '2' as order
from yourtable
union
select ID, sales_typ3_amnt, '3' as order
from yourtable
order by ID, order

Ogmios

[tostay2003]

I need to convert from flat file, so guess i need to try what DSguru2B says. I will let you all know tomorrow after giving it a try

[kcbland]

3 output links from a transformer, use a link collector, turn on Inter-Process row buffering.

[ray.wurlod]

There is a server Pivot stage, and this is exactly the task that it is intended to perform. Use the Pivot stage.