pivoting data in rows to column

linit · Post by **linit** » Thu Nov 02, 2006 11:25 am

Hi,

I am trying to pivot data present in rows into columns using data stage. I have achieved the same using oracle,but i want to implement it using data stage.

My input looks like this.

Item Sls_cd Period Amt

P1 B1 0106 1000
P1 B1 0206 1500
P1 B2 0106 2000

My output should be like this

Item Sls_Cd 0106 0206

P1 B1 1000 1500
P1 B2 2000 0

Can anyone let me know how i can achieve it using data stage.

ray.wurlod · Post by **ray.wurlod** » Thu Nov 02, 2006 1:34 pm

Welcome aboard. :D

The Pivot stage is what you need. It has its own manual (located with all the other manuals on your client machine).

samsuf2002 · Post by **samsuf2002** » Thu Nov 02, 2006 1:40 pm

search for pivot or vertical pivot in the forum it will help u .

gateleys · Post by **gateleys** » Thu Nov 02, 2006 3:55 pm

I don't think the Pivot stage will be of help since it does horizontal pivoting (convert columns into rows). You would need to do vertical pivoting. One good post to help you is -
viewtopic.php?t=85702&highlight=vertica ... 7cc107e5dd

gateleys

udaysindu · Post by **udaysindu** » Sat Sep 22, 2007 10:11 pm

can i know how i have to convert rows into coumns.Example i have 3 rows i need these rows i have load 1 row with all data in columns level

ray.wurlod · Post by **ray.wurlod** » Sun Sep 23, 2007 4:22 am

Procedural Note
We don't hijack threads. A new topic requires a new thread, in the appropriate forum.

chucksmith · Post by **chucksmith** » Sun Sep 23, 2007 7:35 pm

This is not too difficult. In a transform stage set up four stage variables: ThisRowKey, NewKey, PrevRowKey, and MoreData. The derivation for ThisRowKey should be the columns that uniquely identify the rows you want to group. The derivation for NewKey is

Code: Select all

If ThisRowKey <> PrevRowKey Then @TRUE Else @FALSE

. The derivation for PrevRowKey is

Code: Select all

ThisRowKey

. The derivation for MoreData is

Code: Select all

If NewKey Then {Your data column} Else MoreData : " " : {Your data column}

.

Now, you have what you need to build your output row. Connect the output link from the transform stage to an aggregator stage.

In the Aggregator stage, group on you row's key columns, and use the LAST derivation for your MoreData column.

This is a simple and fast solution. It works best if you enable interprocess communications on your Job Properties performance tab.

monaz · Post by **monaz** » Mon Sep 08, 2008 4:37 am

Thanks, i have applied the same logic what you specified. Are we not going to use any constraints.

monaz · Post by **monaz** » Mon Sep 08, 2008 4:58 am

My requirement is that for example

INPUT is given below
empid line_num Text
100 1 a
100 2 b
100 3 c
200 1 aa
200 2 bb
300 1 ccc

OUTPUT should be:-
emp text
100 abc
200 aabb
300 ccc

I have applied the same logic as given above. but i am not the output as above.

Instead the output i am getting is
emp text
100 a
200 aa

Please advice me how to go about this...

bkumar103 · Post by **bkumar103** » Mon Sep 08, 2008 7:13 am

You can code like this
suppose col1 is the key..

then define the three stage variable stgvar1, stgvar2 and atgvar3
the derivation for stage varibles might be like as:

stgvar1:
if trim(DSLink2.col1) = trim(stgvar4) then 1 else 0
stgvar2:
if stgvar1 = 1 then trim(stgvar2):trim(DSLink2.col3) else trim(DSLink2.col3)
stgvar3:
trim(DSLink2.col1)

the output from the transformer can be passed to hash file or aggregator stage to remove the duplicate based on the key.

Thanks,