How to convert multiple rows into single row in datastage

Hi,
Can you please solve this in datastage..
my input is
name,city
johnson,newyork
johnson,mumbai
johnson,delhi
want output as
name,city1,city2,city3
johnson,newyork,mumbai,delhi
pls explain with steps
thanking in advance...

Showing Answers 1 - 13 of 13 Answers

sudip

  • Apr 23rd, 2015
 

Seq stg------>Sort stg--------> Trnsfrm stg-------->Tgt
1. read data in seq file stage.
2. in sort stage enable key column change as true.
3. in Trnsfrm define stage var, logic -> if key change =1 thn dslinkcity else stage var:,:dslinkcity
4. now define 3 col in output tab in trnfrm and use field function, lyk field(stage var,,,1) for city1 thn field(stage var,,,2) for city2 and field(stage var,,,3) for city3
hope this will work.

  Was this answer useful?  Yes

Nish

  • May 25th, 2015
 

Hi there are 2 solutions to this scenario. It can be easily accomplished using the Pivot enterprise stage from DS 8.5 onwards by enabling the vertical pivoting aspect. Alternatively from DS 8.5 onwards you can also use Looping in Transformer.

When it comes to DS 8.0.1 these features are unavailable, so here you have to couple remove duplicates with a transformer.

@Sudip your answer is partly correct, yes the logic produces result but we are interested only in the last row of each name as it completely fills all 3 cities so staging the transformer into a remove duplicate stage enable retain last and output. this will result in inverse pivoting in earlier versions.

  Was this answer useful?  Yes

Sudip Barman

  • May 27th, 2015
 

Hi Nish,

Yes you are correct. I missed that RM Dup stage.
So the design will be:
Seq stg------>Sort stg--------> Trnsfrm stg-------->Remove Dup stage--------->Trnsfrm stg-------->Tgt

1. read data in seq file stage.
2. in sort stage enable key column change as true.
3. in Trnsfrm define stage var, logic -> if key change =1 thn dslinkcity else stage var:,:dslinkcity
4. In remove dup stage enable retain last option and mapped output to next stage.
5. now take another Trnsfrm define 3 col in output tab in trnfrm and use field function, lyk field(stage var,,,1) for city1 thn field(stage var,,,2) for city2 and field(stage var,,,3) for city3
hope this will Help

  Was this answer useful?  Yes

Sudip Barman

  • May 27th, 2015
 

Hi Nish,
You are correct. I missed RM dup stage.
So the job design will be:
Seq stg------>Sort stg-------->Trnsfrm stg1----->Renove Dup----->Trnsfrm stg2-------->Tgt
1. read data in seq file stage.
2. in sort stage enable key column change as true.
3. in Trnsfrm define stage var, logic -> if key change =1 thn dslinkcity else stage var:,:dslinkcity
4. in remove dup stage enable retain the last value option.
5. now take another Trnsfrm stage define 3 col in output tab city1, city2 and city3 in trnfrm and use field function, lyk field(stage var,,,1) for city1 thn field(stage var,,,2) for city2 and field(stage var,,,3) for city3
also this can be done using pivot stage.
hope this will help.

  Was this answer useful?  Yes

santhosh

  • Jun 1st, 2015
 

We can directly achieve this by using Pivot stage

  Was this answer useful?  Yes

DS

  • Aug 19th, 2015
 

Hi,
I tried using pivot expertise vertial option but it is not working since all the funtions are related to arithmetic like average,min,max. could you please tell me which function to use

  Was this answer useful?  Yes

RAM

  • Aug 28th, 2015
 

You can use the following steps.
SRC --> SORT (by name) provide Key change=1 -->Transformer--> Remove Duplicates(Key as Name) (need to set property as duplicate for last one).
Transformer stage: you can get two stage variables.
1.svName 2) svAllctys.
svName Derivation: If keychange column=1 then y else n
svAllctys Derivation: If svName=y then CTY else Allctys:,CTY
then you will get the data after transform stage like below.
johnson,newyork
johnson,newyork,mumbai
johnoson,newyork,mumbai,delhi
On Remove Duplicate stage: you ommit the 1 & 2 record and get the last record in target.
Name, CTY
johnoson, newyork,mumbai,delhi
Pls try it.

  Was this answer useful?  Yes

Manas Mishra

  • Sep 1st, 2015
 

This is a very simple requirement that can be achieved easily by just one transformer stage.
SeqFile--->Tranformer---->target(file/db)
Logic:
Read your file by setting up properties needed.
Transformer:
In the staget property in Input tab select hast partition, make your input column Name as key for Partitioning and Sorting.
In the stage variable make three stage variable in following ways:
1) svCur => link.Name
2) svConcat => if svCur = svPrev Then svConcat:,:link.City Else link.City
3) svPrev => link.Name
Output:
In the contraint give LastRowInGroup(link.Name)
Make four output columns
Name = link.Name
City1 = Field(link.Name,,,1)
City2 = Field(link.Name,,,2)
City3 = Field(link.Name,,,3)

  Was this answer useful?  Yes

manish

  • Oct 5th, 2015
 

In case in INPUT city is same like, Delhi and Delhi then as per your answer is
Johnson, Delhi, Delhi
so it is wrong, in case I want
Johnson, Delhi
then what I have to do?

  Was this answer useful?  Yes

Reddy

  • Oct 20th, 2015
 

seq/orcle stg-->sort_stg--->Trns_stg--->RD--->Target
Steps
-------------------
step1: sort_stg:
key=name
sort key mode: if it is sorted order take ((previous sorted)Dont sort) or (Sort) it
Createkeychangecolumn: true
it will add extra column keychange() column
step2: Trns_stg:
take one stage variable in transformer stage
SV: if dslink.keychange=1 then dslink.city else sv:,:dslink.city
map columns
name ---------> name
SV-------------->city
Step3: RD
keycolumn=name
Duplicate to retain: last
U will get a desired output.

varun negi

  • Dec 8th, 2015
 

* You can do a sort and RD on name and city combination.
* Pass the output to copy stage followed by aggregator stage to calculate number of rows and then join the aggregator output link with another link with copy stage.
* Lets say we got the count in CNT column from aggregator.
* In transformer stage, initialize svCurrname with initial value = "-1" and then declare a new variable svPrevname as : If svCurrname = "-1" Then name Else svCurrname.
* Have another variable with svCurrname = name.
* Run the loop in Transformer stage as: @iteration <= cnt.
* Initialize the loop variable: svCititesString with initial value as "-1". Then write the derivation as - If svPrevname = name Then (If svCitiesString = "-1" Then City Else svCitiesString : "," : City) Else City.
* In the output of transformer, define the name, and in map svCitiesString loop variable to city column.

  Was this answer useful?  Yes

Neha

  • Jan 2nd, 2016
 

We can directly use pivot job just set the properties and you can get the desired output.

  Was this answer useful?  Yes

Ram

  • Mar 7th, 2016
 

You can do use pivot enterprise and give the properties as pivot for cty and group by name.
It will work.
Input --> Pivot Enterprise (vertical pivot) --> trg.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions