Multiple rows to single row comma separated values
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
Multiple rows to single row comma separated values
Hi All,
I have a requirement which is something like this. The input column will have values like :-
1
2
3
4
that is input column values are in single column which needs to be output as comma separated values in single column and single row like :-
1,2,3,4
Please help me in achieving this logic. I have tried column import stage but I am not getting the desired output. I tried to split the single column into multiple columns but the output generated is one output column getting all the values in different rows and rest of the 3 columns have no values, it should have been all the 4 columns getting the values and 1 single row.
Thanks to all in advance.
I have a requirement which is something like this. The input column will have values like :-
1
2
3
4
that is input column values are in single column which needs to be output as comma separated values in single column and single row like :-
1,2,3,4
Please help me in achieving this logic. I have tried column import stage but I am not getting the desired output. I tried to split the single column into multiple columns but the output generated is one output column getting all the values in different rows and rest of the 3 columns have no values, it should have been all the 4 columns getting the values and 1 single row.
Thanks to all in advance.
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
Thanks for the reply. I tried the pivot stage with the vertical pivot property but how to configure the stage . For 4 output columns I choose the derivation as the same input column, the job aborts and even the mapping seems to be wrong.chulett wrote:That would be a "vertical pivot", you can search the forums for the tecniques involved. Only thing "extra" you'd need to do is take the separate columns the pivout would generate and concatenate them ...
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
I was able to configure the pivot stage but the output is not correct, it splits the records into 4 columns and 4 rows. The output is like this :-chulett wrote:That would be a "vertical pivot", you can search the forums for the tecniques involved. Only thing "extra" you'd need to do is take the separate columns the pivout would generate and concatenate them ...
Col1 col2 col3 col4
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
Then you didn't configure it quite correctly. The first column should be marked as a key and you'll need to create a fake key column for that with a constant value so the data values can pivot up properly. You can drop the fake column after the pivot.pravin1581 wrote:I was able to configure the pivot stage but the output is not correct, it splits the records into 4 columns and 4 rows.
Last edited by chulett on Fri Sep 10, 2010 5:24 pm, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
This should work. May be try defining the initial value of your stage variable to '' {Empty}pravin1581 wrote:This doesn't works . The output is coming as 0,0,0,0,
If you are mapping the column properly, how come this doesnt work? No complex thing involved in this. You are just appending your column to the stage variable. I have did this before and it worked. All you need to do is to check if everything is setup properly.
Arun
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom