Best approach to design?

[scope123]

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.

[scope123]

Can anyone suggest me?

[chulett]

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?

[scope123]

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.

[priyadarshikunal]

are you sure you got the answer?

It can be done with a horizontal pivot followed by a vertical pivot however the number of columns should be fixed, aleast the max number of columns.

[scope123]

Yes the Number of columns are Fixed That is (A1 Series) is 3 and (Value Series) is 3

[scope123]

So it will be the combination of Vertical Pivot and Horizontal Pivot.
Can any one just give an outline of the design.

is any other way to do the same in Datastage?