Problems changing rows in column

sylvan_rydes · Post by **sylvan_rydes** » Mon Aug 21, 2006 3:48 pm

Hi All,

I have this problem as described below-

I have input records as below:
P Q (P and Q are column names)
1. A
2. B
3. C
4. D
5. E
6. F

Now I need output like

W X Y Z (W, X, Y and Z are column names)
1. A B C
2. D E
3. F ..

Any help will be appreciated. Thanks in advance.

Sylvan Rydes

meena · Post by **meena** » Mon Aug 21, 2006 5:19 pm

Hi,
I think you can use Row merger active plugin stage. You can do a search in the forum, you will get more info. And move this post to server.This is a server job

DSguru2B · Post by **DSguru2B** » Mon Aug 21, 2006 6:38 pm

Rows into columns huh, that sounds like a job for the pivot stage. Look into the pivot stage along with the row merger.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 21, 2006 9:49 pm

No, the requirement is for a vertical pivot. The Pivot stage only does horizontal pivots.

I can not see any logic in the example for determining which rows accumulate into columns. Did you give erroneous values for the P column?

Search the forum for "Vertical Pivot" to find some techniques. Make sure that the data are grouped (partitioned) by the P column.

sylvan_rydes · Post by **sylvan_rydes** » Tue Aug 22, 2006 5:17 pm

Hi All,

Thanks for all the replies. The only problem is that, there is no defined rules for the record. It depends on its serial no. We used store procedure and it works fine.

Thanks again.

Sylvan_rydes

DSguru2B · Post by **DSguru2B** » Tue Aug 22, 2006 7:06 pm

Even via datastage you can get this done but we need logic. I bet even the stored procedure executes some sort of logic. If you can get us that logic maybe someone can help you design you, or even walk you through the entire job.

sylvan_rydes · Post by **sylvan_rydes** » Wed Aug 23, 2006 4:41 pm

Hi All,

I m here to define the problem in detail. We have two address input. But one address to copied 3 times and the second one is copied 2 times in a target. All address belong to same person. Now for two address we have output table having 5 serial number for address. In next table we are again dividing these 5 in two but using the serial number now.

In store procedure we are generating same number in both the target tables but in datastage as we ave one job per target table so it is becoming a problem.

Thanks again for all your help.

Sylvan_rydes

ray.wurlod · Post by **ray.wurlod** » Thu Aug 24, 2006 12:24 am

So, basically, the requirement is to sort by address and group by (a newly generated) serial number?

Sort the addresses with a Sort stage, then use a Transformer stage to detect changes and generate the serial number, incrementing when the value changes. Run in sequential mode or partition by hash on address so that all members of each group are on the same partition.