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.
Pivot Column to Row.
Moderators: chulett, rschirm, roy
Re: Pivot Column to Row.
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.
Thanks.
DS4DRIVER wrote: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.
Re: Pivot Column to Row.
I'm not really following what you mean by 'the other way round'.DS4DRIVER wrote: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.
![Confused :?](./images/smilies/icon_confused.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
"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
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
Re: Pivot Column to Row.
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.
"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.
DS4DRIVER wrote: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.
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![Smile :-)](./images/smilies/icon_smile.gif)
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
![Smile :-)](./images/smilies/icon_smile.gif)
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn