many records to one

DSbox61 · Post by **DSbox61** » Fri Dec 29, 2006 10:22 am

Hi ppl,

I have a job where i have 3 records coming from DB2(1)
C1|A1
C1|A2
C2|B1
and 2 records coming from DB2(2)
C1|X1
C2|X2
In the output file i want
C1|A1A2|X1
C2|B1|X2

DB2(1)==>Transformer==>Flat File
^
||
||
DB2(2)

thnxxx...

DSguru2B · Post by **DSguru2B** » Fri Dec 29, 2006 10:25 am

First you need to perform vertical pivoting on each input. Once you have that done. Then store one input into a hashed file. Let the other be the input. Do a lookup on your first column and then concatenate the records together with a pipe in between.
Search the forum for vertical pivoting.

chulett · Post by **chulett** » Fri Dec 29, 2006 10:34 am

In other words, you'll need to build a single record from the multiple incoming records yourself via concatenation in stage variables. As noted, you can search the forum for many discussions on the subject of a 'vertical pivot' which is what that would be called when you do rows to columns.

Columns to rows is called a 'horizontal pivot' and there's a stage available to accomplish that.

DSbox61 · Post by **DSbox61** » Fri Dec 29, 2006 3:03 pm

But we cannot achieve vertical pivot through pivot stage in server edition rifght!

narasimha · Post by **narasimha** » Fri Dec 29, 2006 3:22 pm

Right.
Also, there is no built in stage for doing a vertical pivot.
You can achieve it, using alternative methods which is discussed many times in this forum

DSguru2B · Post by **DSguru2B** » Fri Dec 29, 2006 3:24 pm

Search my friend search. Its not that hard...

us1aslam1us · Post by **us1aslam1us** » Fri Dec 29, 2006 4:47 pm

Especially this one which is a perfect solution to your problem.

viewtopic.php?t=97681&highlight=vertical+pivot

Sam

DSbox61 · Post by **DSbox61** » Sat Dec 30, 2006 2:02 pm

yes sam, even i implemented the same u mentioned.....it really is the perfect solution......thnx 2 all gurus...and thnx to jim. :D