Multiple rows to single row
Moderators: chulett, rschirm, roy
Multiple rows to single row
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,
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,
Bhanu
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Bhanu
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
@Ray- Could you please suggest how to generate this part
@bond88- You may try the below in transformer to get the desired output.
1.In stage variable-
Output Derivation-
2.In remove Duplicate stage keep the last record in the group.
in pivot enterprise stage.ijkl(PDPI);
@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
Code: Select all
Role Role
SV1 ID
SV2 ID2
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: