Page 1 of 1
Multiple rows to single row
Posted: Mon Dec 03, 2012 1:28 pm
by bond88
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,
Posted: Mon Dec 03, 2012 2:28 pm
by ray.wurlod
Yes, it is a vertical, or reverse, pivot. This is described very clearly in the manual. What have you tried?
Posted: Mon Dec 03, 2012 3:24 pm
by bond88
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?
Posted: Mon Dec 03, 2012 3:32 pm
by srividya
If your table is on oracle DB try using Listagg function!!
Posted: Mon Dec 03, 2012 3:43 pm
by bond88
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.
Posted: Mon Dec 03, 2012 10:12 pm
by ray.wurlod
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?
Posted: Mon Dec 03, 2012 10:32 pm
by bond88
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.
Posted: Mon Dec 03, 2012 11:45 pm
by ray.wurlod
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.
Posted: Wed Dec 05, 2012 1:05 am
by bhasds
@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-
2.In remove Duplicate stage keep the last record in the group.
Posted: Wed Dec 05, 2012 2:27 am
by ray.wurlod
One task, one stage.
You do the pivoting in the Pivot stage, you do the transformations in a Transformer stage.