Using PIVOT stages

gpbarsky · Post by **gpbarsky** » Fri Oct 24, 2003 4:26 pm

Hi there.

I would like to know how can I use a PIVOT stage. I didn't find any information on the help manuals.

Thanks in advance :D

chulett · Post by **chulett** » Fri Oct 24, 2003 4:30 pm

If you look in the directory where you installed the DataStage client, there is a "Docs" directory under it. In there are pdfs for all of the plugin stages, including the pivot stage.

gpbarsky · Post by **gpbarsky** » Fri Oct 24, 2003 4:40 pm

Craig:

Thank you for your answer, but I didn't find any information on the manuals (*.pdf) that I have installed in my PC.

Could you send me the PDF containing this information ? My e-mail is gbarsky@osde.com.ar.

Thanks in advance.

chulett · Post by **chulett** » Fri Oct 24, 2003 8:34 pm

I'm sorry! They exist in my Docs directory because I put them there.

The pdfs for the plugin stages are included in the server install folders on your cdrom. Drill down thru that and you should find them. If you still can't turn them up, I'll email it to you tomorrow.

degraciavg · Post by **degraciavg** » Fri Oct 24, 2003 8:37 pm

gpbarsky wrote:Hi there.

I would like to know how can I use a PIVOT stage. I didn't find any information on the help manuals.

Thanks in advance :D

hi,

briefly, the Pivot stage is simply a one-way "transpose" stage - used for transposing your columns into rows only. you can't use it to transpose rows into columns (i use the aggregator stage to do this... but it works only on a certain conditions).

to use it,
1. put all the columns you want to transpose into the input tab
2. put the target column in the output tab
3. in the derivation column, add the columns to transpose separated by commas.

hth,
vladimir

mhester · Post by **mhester** » Sat Oct 25, 2003 7:44 am

There are two ways to pivot data. One is horizontal and the other is vertical. DS supports a horizontal pivot (one to many rows) and does not support a vertical (many rows to one).

I believe the on-line help would be sufficient for you to gain a basic understanding of the pivot stage and can be accessed if you choose help from the pivot stage via the designer.

Regards,

Michael Hester

mjeaslea · Post by **mjeaslea** » Tue Jun 01, 2004 8:31 am

mhester wrote:There are two ways to pivot data. One is horizontal and the other is vertical. DS supports a horizontal pivot (one to many rows) and does not support a vertical (many rows to one).

I believe the on-line help would be sufficient for you to gain a basic understanding of the pivot stage and can be accessed if you choose help from the pivot stage via the designer.

Regards,

Michael Hester

Hi, i'm wondering about the performance of the Pivot operation. I need to translate about 20 million rows from an Oracle 8 Database into an SQL Server 2000 database regularly.

What sort of SQL does the pivot step generate?

Cheers

vmcburney · Post by **vmcburney** » Tue Jun 01, 2004 5:25 pm

The pivot step does not generate any SQL, you will have to link the output from your pivot stage to a database stage either using an insert/update stage or using the faster bulk load stage.

Performance will be improved if you can run multiple instances of your job where each job filters based on a field that will not split up individual pivot record groups. It will also be much faster if you can put in change data detection where you only process changed data at regular intervals.

DSXchange

Using PIVOT stages

Using PIVOT stages

Re: Using PIVOT stages

Performance