Pivot Column to Row.

[DS4DRIVER]

Hi,
Can sombody tell me if there is a way i can change Column data to a row. Example... A file that has column data as shown below. This should be pivoted.

"08/01/2002"
"Requirement"
"CDWP"
"Corporate Data Warehouse"
"Accounting Period Loan Unpaid Principal Balance History"
"acct_per_ln_upb_hs"
"n/a"
"Mortgage"
"Estimated accounting period ending loan UPB and amount of UPB in so"
"n/a"

Thanks.

[chulett]

If you mean a horizontal pivot where one row becomes many rows based on repeating key columns, then (oddly enough) the Pivot Stage is what you are looking for.

[DS4DRIVER]

I agree that Pivot Stage can be used to Pivot one row into many rows. But, this is the otherway round. One column into many columns.

Thanks.

Hi,
Can sombody tell me if there is a way i can change Column data to a row. Example... A file that has column data as shown below. This should be pivoted.

"08/01/2002"
"Requirement"
"CDWP"
"Corporate Data Warehouse"
"Accounting Period Loan Unpaid Principal Balance History"
"acct_per_ln_upb_hs"
"n/a"
"Mortgage"
"Estimated accounting period ending loan UPB and amount of UPB in so"
"n/a"

Thanks.

[chulett]

I agree that Pivot Stage can be used to Pivot one row into many rows. But, this is the otherway round. One column into many columns.

I'm not really following what you mean by 'the other way round'. You can turn one column into many columns by splitting the data (using something like FIELD or via substrings) and simply outputing many columns from the Transformer.

Since I'm guessing that's not really what you meant, it would help to take your example data and show us what the end result should look like, instead of just saying 'it should be pivoted'. That would help alleviate any ambiguity.

[mhester]

I believe what you are asking for is a "vertical" pivot and DS does not have or support this. The pivot stage plugin is a horizontal pivot. If each of the following -

"08/01/2002"
"Requirement"
"CDWP"
"Corporate Data Warehouse"
"Accounting Period Loan Unpaid Principal Balance History"
"acct_per_ln_upb_hs"
"n/a"
"Mortgage"
"Estimated accounting period ending loan UPB and amount of UPB in so"
"n/a"

represents a row of data then you cannot pivot them into a single output row of data. I say no because there is no stage to do this, but there are possibly many ways to accomplish what you want to do.

Regards,

Michael Hester

[DS4DRIVER]

If Column Data is
"ABC"
"BCD"
"CDE"
"DEF"
"EFG"
"FGH"


For 6 rows in one coulmn, the final output should be 1 row with 6 columns.
"ABC"|"BCD"|"CDE"|"DEF"|"EFG"|"FGH"

Is there a way to do this?
I am trying hard to get this done.

Thanks.

Hi,
Can sombody tell me if there is a way i can change Column data to a row. Example... A file that has column data as shown below. This should be pivoted.

"08/01/2002"
"Requirement"
"CDWP"
"Corporate Data Warehouse"
"Accounting Period Loan Unpaid Principal Balance History"
"acct_per_ln_upb_hs"
"n/a"
"Mortgage"
"Estimated accounting period ending loan UPB and amount of UPB in so"
"n/a"

Thanks.

[kiran_kom]

you can do it using a SQL stmt.

You will need a two-part key to accomplish it. Assuminng your data is of the type:


col1 col2 data
1 1 "ABC"
1 2 "BCD"


You can compress it into one row using a self join.

select a.col1, a.data, b.data from tablename a

Left outer join

tablename b
ON

a.col1 = b.col1

where a.col2 = 1 and b.col2 - 2



alternatively read the first row using the odbc stage and pull out the second row using a ODBC lookup.

hope I have confused you enough

[vmcburney]

First option is to do all this in a DataStage routine that opens the file and writes to another file. This routine can then be called from a sequence job. The routine would open the file, go into a loop, read the third column and write it out to another file along with the delimiter. Write it all out as a single line.

Second option is to do it with stage variables in a server job.

1) you need to add a row to the end of your source file, this can be done easily with a before job command. The row you add will have something in the primary key or data field that identifies it as the last row such as 999,999,"DUMMY ROW".

2) create two stage variables, one which holds the concatenated row eg. OUTPUTROW, one which holds the last column value found LASTCOL. Set OUTPUTROW to be OUTPUTROW:LASTCOL. Set LASTCOL to be input.data which will hold the value to be concatenated into a single row.

3) create a constraint to only output a row when you have reached the last row.

You need two stage variables, when you process your first row OUTPUTROW is still empty, it is always one row behind. When you reach your last dummy row OUTPUTROW receives the value from the second last row and outputs everything except the value in your dummy row.

You may hit memory problems on very long spreadsheets, this will work best on shorter spreadsheets.

Third option is to read it in as a sequential file, write out the DATA column to a sequential file and then use a script to go through and replace the end of row markers with a delimiter to turn it into a single delimited row.