Multiple rows to single row

bond88 · Post by **bond88** » Mon Dec 03, 2012 1:28 pm

Hi all,
Can anyone please help me with this issue?

Issue:
Data in a table is like this

Role ID ID2 Var

112627 82839 abcd COPDPI
112627 85899 efgh COPDPI
112627 78209 ijkl PDPI

I want to convert it into (descending order by Role)

Role ID ID2(var)

112627 [78209],[82839],[85899] ijkl(PDPI);abcd(COPDPI);efgh(COPDPI)

I searched previous topics but those doesn't resolve my issue. I came to know there are two ways to do it one is by using transformer and another one is by using pivot (reverse pivot). Please explain the process/method in detail.

Thank you,

ray.wurlod · Post by **ray.wurlod** » Mon Dec 03, 2012 2:28 pm

Yes, it is a vertical, or reverse, pivot. This is described very clearly in the manual. What have you tried?

bond88 · Post by **bond88** » Mon Dec 03, 2012 3:24 pm

Thanks Ray,
I am using transformer stage to achieve reverse pivot but it doesn't work for me. I saw so many posts those are based on server jobs and I am looking for parallel (Px) job. Could you please guide me?

srividya · Post by **srividya** » Mon Dec 03, 2012 3:32 pm

If your table is on oracle DB try using Listagg function!!

bond88 · Post by **bond88** » Mon Dec 03, 2012 3:43 pm

Sri Vidya,
I am getting data from different source. I can't able to use Listagg function. By using pivot enterprise stage and by selecting vertical pivot I am able to do this. But it is asking array size. Array size is not same for all key's and that too I need all the columns data in to one column I think that I can achieve that by using transformer stage after pivot stage. But how can I handle the array size? Any input please.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 03, 2012 10:12 pm

Why are you using the Transformer stage? The PXPivot stage can do vertical pivoting such as you require with a lot less effort.

Sure, it can be done with a Transformer stage, but why would you bother?

If you insist on using a Transformer stage (it's a free country, or so we're assured, where you are) how about you start by telling us what you've attempted and what "doesn't work" means in that context?

bond88 · Post by **bond88** » Mon Dec 03, 2012 10:32 pm

ray.wurlod wrote:Why are you using the Transformer stage? The PXPivot stage can do vertical pivoting such as you require with a lot less effort.

Sure, it can be done with a Transformer stage, but why would ...

Hi Ray,
I am not able to see your message completely.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 03, 2012 11:45 pm

For less than 30c per day you can get yourself a premium membership and then you would be able to. Premium memberships are how the hosting and bandwidth costs of DSXchange are met, so you'd be helping the site to live on. Did you notice the lack of ads? Details on/from the DSXchange home page.

bhasds · Post by **bhasds** » Wed Dec 05, 2012 1:05 am

@Ray- Could you please suggest how to generate this part

ijkl(PDPI);

in pivot enterprise stage.

@bond88- You may try the below in transformer to get the desired output.

1.In stage variable-

Code: Select all

If ToT.Role <> SV3 Then "[":ID:"]" Else SV1:",": "[":ID:"]"  SV1
If Role <> SV3 Then ID2 Else SV2:";":ID2:"(":Var:")"  SV2
Role   SV3

Output Derivation-

Code: Select all

Role Role
SV1 ID
SV2 ID2

2.In remove Duplicate stage keep the last record in the group.

ray.wurlod · Post by **ray.wurlod** » Wed Dec 05, 2012 2:27 am

One task, one stage.

You do the pivoting in the Pivot stage, you do the transformations in a Transformer stage.