Multiple rows from a single row

[mak]

Hi,

Please guide me to handle the following situation..

I have a table with the following structure.

Src Table : X
Columns : key1,key2,User_type1,UserQy1,User_type2,User_Qy2
My requirement is to split this to

Target table: Y
key1,key2,seqno,user_type1,user_qy1
key1,key2,seqno,user_type2,user_qy2

Here seqno is to avoid duplicates.it is part of composit key in target table

1. How to split into tow rows.
2. How to generate seqno. For each key1,key2 combination seqno should be like 1,2.

Infact in the original table i have 15 User_type columns each row. so i need to split into 15 (or X rows) rows with condition User_type<>' '
seqno would be 1 to 15 ( or X) for each key1,key2 combination.

Both Source and Target are DB2.

Thanks in Advance.

[chucksmith]

Break your rows using the Pivot stage.

Code: Select all

SRC ---> XFM ---> Pivot ---> XFM ---> Dest

You can use stage variables to compute your sequence numbers in the second transform. When you detect a change in the values of key1 and key 2 relative to the previous row, reset the sequence number value to 1. Also, you can drop any null rows, too.

[vmcburney]

I would certainly try the pivot design first. If peformance isn't good enough then you can do it using a long user defined SQL statement in a DB2 stage. Something along the lines of:

SELECT key1,key2,User_type1,UserQy1, 1 'seqno' FROM TABLENAME
UNION key1,key2,User_type2,User_Qy2, 2 FROM TABLENAME
UNION key1,key2,User_type3,User_Qy3, 3 FROM TABLENAME
ETC

Not as elegant as a pivot stage and shifts processing load from the DS server to the source DB2 database.

[mak]

Smith,Vincent

Thanks for your replies.

But i dont see Pivot Stage in my Palette/processing category in desinger?
Does that come automatically installed with DS or do i need to install any library file ?

Thanks

[Sainath.Srinivasan]

It is installed in DataStage by default. You may have to pull the scrollbar down to locate it.

[mak]

Sainath,

I dont see it. Could you pls give me the name of library file?

Thanks

[Sainath.Srinivasan]

Your stages are arranged in Alphabetical order of their names. So the pivot can be after 'Merge' and before 'Row-Merge'.

Alternatively, someone may be brought it into your 'Favorites' section.

[KeithM]

The pivot stage is not installed automatically when datastage is installed. It is a plugin that is an option when installing the datastage server. If you are not seeing it I would guess that it is not installed. Check the Plug-in installation guide for details on how to get the Pivot installed in your environment.

[mak]

Thanks Everybody

I sent a request to install this plug-in
Once i have this i will use this to design my process then i might have some more questions.