Hi experts,
I have requirement below.
I mean that the output table columns are dynamic i.e A1..........An
EMP COL_NM value1 value2 value3
==== ===== ==== ==== ====
100 A1 "NAME"
100 A2 100.00
200 A1 "NAME1"
200 A3 "Y"
OUTPUT TABLE
==========
EMP VALUES A1 A2 A3 A4 A5 ..... A500
=== ===== === == == == === ======
100 value1 NAME 100.00
100 value2 NULL NULL
100 value3 NULL NULL
200 value1 NAME1 Y
200 value2 NULL NULL
200 value3 NULL NULL
Please provide your suggestions guys.
i know that it can be done by using vertical pivot.
But here columns are converted to rows(Value1....Value100) and rows to columns(Column Name A1....An)
Thank you for your time.
Best approach to design?
Moderators: chulett, rschirm, roy
Best approach to design?
scope123
Well... let's start by having you explain what "the output table columns are dynamic" means. There's no fixed number of columns? There's no fixed column names? Not sure how that's possible unless you are creating the "output table" each time the job runs.
Can you shed some light on that, please?
Can you shed some light on that, please?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I have got the solution in SQL.
Here Value1,Value2,Value3 Columns Values are numbers from source
Select Emp,SValues,Sum(Case when COL_NM='A1' Then Sdummy Else Null End) AS A1,
Sum(Case when COL_NM='A2' Then Sdummy Else Null End) AS A2,
Sum(Case when COL_NM='A3' Then Sdummy Else Null End) AS A3
FROM TABLENAME Unpivot(Sdummy for Svalues in ('Value1','Value2','Value3') as Sdummy
Group by Emp,Svalues
I need to do the same thing in Datastage.
Can any one advice me.
Here Value1,Value2,Value3 Columns Values are numbers from source
Select Emp,SValues,Sum(Case when COL_NM='A1' Then Sdummy Else Null End) AS A1,
Sum(Case when COL_NM='A2' Then Sdummy Else Null End) AS A2,
Sum(Case when COL_NM='A3' Then Sdummy Else Null End) AS A3
FROM TABLENAME Unpivot(Sdummy for Svalues in ('Value1','Value2','Value3') as Sdummy
Group by Emp,Svalues
I need to do the same thing in Datastage.
Can any one advice me.
scope123
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI