Datastage Realtime Scenario:
Requirement: When you have source data in multiple columns and you may need to make it as Horizontal. i.e., Reformat the data in multiple rows as given in the below example.
Example : Input -
Table name - Student_Marks
Requirement: When you have source data in multiple columns and you may need to make it as Horizontal. i.e., Reformat the data in multiple rows as given in the below example.
Example : Input -
Table name - Student_Marks
Name, Course1,Course2,Course3
John,23,34,56
Daniel, 45,23,63
Damian,45,34,576
Output
Name,Course
John,23
John,34
John,56
Daniel,45
Daniel,23
Daniel,63
Damian,45
Damian,34
Damian,576
Solution:
To fullfil the above requirement we can go for oracle query or can design a solution in Datastage with pivot stage or Transformer stage.
Oracle Query(11g Onwards only):
Select * from Student_Marks
UNPIVOT include NULLS (Course for Course_Marks in
(Course1 as 'C1' , Course2 as 'C2' , Course3 as 'C3' ) );
Datastage Design:
Next to the source data read stage(seqntial/database stage), place a transformer stage and do the following piece of code in it.
Loop Condition
Loop While : @ITERATION<=3
Else If @ITERATION=2 Then DSLink3.course2 LoopVar
Else DSLink3.course3
Derivation
1. DSLink3.Name -----> Name
2. LoopVar -----> Course
Please send me email /post if any questions.