Multiple rows from a single row
Moderators: chulett, rschirm, roy
Multiple rows from a single row
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.
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.
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
Break your rows using the Pivot stage.
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.
Code: Select all
SRC ---> XFM ---> Pivot ---> XFM ---> Dest
Chuck Smith
www.anotheritco.com
www.anotheritco.com
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
Keith