Multiple rows to single row comma separated values

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

pravin1581
Premium Member
Premium Member
Posts: 497
Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:

Multiple rows to single row comma separated values

Post by pravin1581 »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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 into a single column post-pivot.
-craig

"You can never have too many knives" -- Logan Nine Fingers
pravin1581
Premium Member
Premium Member
Posts: 497
Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:

Post by pravin1581 »

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 ...
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.
pravin1581
Premium Member
Premium Member
Posts: 497
Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:

Post by pravin1581 »

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 ...
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 :-

Col1 col2 col3 col4
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

Read the source and write an output with record delimiter as comma
pravin1581
Premium Member
Premium Member
Posts: 497
Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:

Post by pravin1581 »

Sainath.Srinivasan wrote:Read the source and write an output with record delimiter as comma
Thanks for the reply. tried that it splits the records into 4 rows as :-

1,
2,
3,
4,

The output should be in single row.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
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.
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
arunkumarmm
Participant
Posts: 246
Joined: Mon Jun 30, 2008 3:22 am
Location: New York
Contact:

Post by arunkumarmm »

Or even in a simpler way, create a stage variable like this:

sv1 = sv1 : ',' : InputColumn

and map it to the target column derivation.

The last output row will have all the values...
Arun
pravin1581
Premium Member
Premium Member
Posts: 497
Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:

Post by pravin1581 »

I defined one of the output columns as key and mapped that to the output along with the rest of the pivot columns. The output is coming as :-

col1 col2 col3 col4 col 5
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
pravin1581
Premium Member
Premium Member
Posts: 497
Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:

Post by pravin1581 »

arunkumarmm wrote:Or even in a simpler way, create a stage variable like this:

sv1 = sv1 : ',' : InputColumn

and map it to the target column derivation.

The last output row will have all the values...
This doesn't works . The output is coming as 0,0,0,0,
arunkumarmm
Participant
Posts: 246
Joined: Mon Jun 30, 2008 3:22 am
Location: New York
Contact:

Post by arunkumarmm »

pravin1581 wrote:This doesn't works . The output is coming as 0,0,0,0,
This should work. May be try defining the initial value of your stage variable to '' {Empty}

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
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

pravin1581 wrote:
Sainath.Srinivasan wrote:Read the source and write an output with record delimiter as comma
Thanks for the reply. tried that it splits the records into 4 rows as :-

1,
2,
3,
4,

The output should be in single row.
What was your field delimiter and record delimiter ?
pravin1581
Premium Member
Premium Member
Posts: 497
Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:

Post by pravin1581 »

Sainath.Srinivasan wrote:Read the source and write an output with record delimiter as comma
Thanks Sai. I tried this , the output is coming as 1,2,3,4,
pravin1581
Premium Member
Premium Member
Posts: 497
Joined: Sun Dec 17, 2006 11:52 pm
Location: Kolkata
Contact:

Post by pravin1581 »

pravin1581 wrote:
Sainath.Srinivasan wrote:Read the source and write an output with record delimiter as comma
Thanks Sai. I tried this , the output is coming as 1,2,3,4,
Any help will be appreciated.
Sainath.Srinivasan
Participant
Posts: 3337
Joined: Mon Jan 17, 2005 4:49 am
Location: United Kingdom

Post by Sainath.Srinivasan »

Isn't that what you asked for ? Trim the trailing comma.
Post Reply