Splitting the rows

paranoid · Post by **paranoid** » Mon Jun 28, 2010 8:36 pm

Hi,

We have a table where the layout looks as below:

BRANCH JOBNO REGION DISTRIBUTOR SALESMAN1 SALESMANPCT1 SALESMAN2 SALESMANPCT2 SALESMAN3 SALESMANPCT3

We are supposed to fetch the records into a new table with the following layout:

BRANCH JOBNO REGION DISTRIBUTOR SALESMAN SALESMANPCT

We could fetch the records in to the new table using the UNION operator as follows:

SELECT BRANCH,
JOBNUMBER,
DISTRIBUTOR,
SALESMAN1,
SALESMANPCT
FROM SALESMAN1)
UNION
(SELECT BRANCH,
JOBNUMBER,
DISTRIBUTOR,
SALESMAN2,
SALESMANPCT2
FROM SALESMAN2)

UNION
(SELECT BRANCH,
JOBNUMBER,
DISTRIBUTOR,
SALESMAN3,
SALESMANPCT3
FROM SALESMAN3)

But i would like to do the same thing using datastage job as well. Please advise.

Thanks
Sue

ray.wurlod · Post by **ray.wurlod** » Mon Jun 28, 2010 9:01 pm

What you seek to do is called a "horizontal pivot" and is performed by the Pivot stage in DataStage.

paranoid · Post by **paranoid** » Mon Jun 28, 2010 9:37 pm

Ray,

Thank you for your swift response. I am currently running a test job to do this using pivot stage. But i dont find a pdf in my installation directory:
C:\Program Files\Ascential\DataStage7.5\Docs\

I dont have a installation CD also..

Can you help me providing that pdf?

Thanks
Sue

paranoid · Post by **paranoid** » Mon Jun 28, 2010 9:39 pm

Also, I could see only one Pivot stage in my Processing group in the designer. I am using DS 7.5.1

Thanks

Sue

ray.wurlod · Post by **ray.wurlod** » Mon Jun 28, 2010 9:45 pm

There is only one pivot stage in version 7.5.

Search on your client machine for a document called Pivot.pdf - this is the manual.

chulett · Post by **chulett** » Mon Jun 28, 2010 9:46 pm

There is only one Pivot stage and it only does horizontal pivots. There should also be a Help button in the stage which explains how to use the stage.

paranoid · Post by **paranoid** » Mon Jun 28, 2010 9:52 pm

Chulett,

I tried 'Help' also but it is more of theoretical rather than practical(that provides with an example).

Thanks

Sue

chulett · Post by **chulett** » Mon Jun 28, 2010 10:04 pm

Do you have a specific question on the stage or need some help getting the output you are after?

paranoid · Post by **paranoid** » Mon Jun 28, 2010 10:15 pm

Chulett,

I was struggling on using the derivation, but after some tries found it:

Output Columsn SPCT : SPCT1,SPCT2,SPCT3(Derivation Used)

Is there any way to get distinct records in pivot stage itself? Or Do i need to use Sort after pivot?

Please advise.

Sue

chulett · Post by **chulett** » Mon Jun 28, 2010 10:42 pm

Looks good and any 'Key' columns will be repeated on each row. There is no distinctness to a pivot, you'd need to enforce that downstream.

paranoid · Post by **paranoid** » Mon Jun 28, 2010 10:51 pm

Thank You So Much Chulett.

Will let you know once i get my desired result.