Best approach to design?

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
scope123
Participant
Posts: 9
Joined: Tue Jun 15, 2010 10:32 am

Best approach to design?

Post by 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
scope123
Participant
Posts: 9
Joined: Tue Jun 15, 2010 10:32 am

Post by scope123 »

Can anyone suggest me?
scope123
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by 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?
-craig

"You can never have too many knives" -- Logan Nine Fingers
scope123
Participant
Posts: 9
Joined: Tue Jun 15, 2010 10:32 am

Post by 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.
scope123
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by 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. :?
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
scope123
Participant
Posts: 9
Joined: Tue Jun 15, 2010 10:32 am

Post by scope123 »

Yes the Number of columns are Fixed That is (A1 Series) is 3 and (Value Series) is 3
scope123
scope123
Participant
Posts: 9
Joined: Tue Jun 15, 2010 10:32 am

Post by 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?
scope123
Post Reply