How to convert each row into a column
Moderators: chulett, rschirm, roy
How to convert each row into a column
I have a source data coming as below
INSERT
20120825
20120905
1
X58065
OT
RP
N
INSERT Check
7842504 7842504
7841631 7841631
7840441 7840441
7840768 7840768
2884402 2884402
4909301 4909301
4933665 4933665
4854045 4854045
2243645 2243645
7286322 7286322
2851545 2851545
2203371 2203371
2147107 2147107
The data in the first row should go into col1, 2nd row should go into col2, 3rd row col3 and so on till 10th row to col10
all Data from 11th row should go into col11
can anyone please give me an idea on how to get this logic working.
Thanks !
INSERT
20120825
20120905
1
X58065
OT
RP
N
INSERT Check
7842504 7842504
7841631 7841631
7840441 7840441
7840768 7840768
2884402 2884402
4909301 4909301
4933665 4933665
4854045 4854045
2243645 2243645
7286322 7286322
2851545 2851545
2203371 2203371
2147107 2147107
The data in the first row should go into col1, 2nd row should go into col2, 3rd row col3 and so on till 10th row to col10
all Data from 11th row should go into col11
can anyone please give me an idea on how to get this logic working.
Thanks !
Add a dummy-column to your input containing a constant value. Then use a vertical-pivot to fill your columns as needed (as documented in Parallel Job Developer's Guide).
You can drop the dummy-column after the pivot. I have to admit, though, that this kind of data-delivery looks rather strange to me. You will have to read all columns as containing identical datatypes (Varchar[max=15]).
You can drop the dummy-column after the pivot. I have to admit, though, that this kind of data-delivery looks rather strange to me. You will have to read all columns as containing identical datatypes (Varchar[max=15]).
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Hi,
Column that need to be converted are static or dynammic?
Column that need to be converted are static or dynammic?
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
I am not sure if vertical pivot works in this case because from 11th row till end of the file the data should go into one column.
I am trying to use vertical pivot and not sure on what to groupby and which column to pivot on. I have two input columns
column1 - varchar(255)
column2-integer - constant value 1
and the output should have 11 columns
I am trying to use vertical pivot and not sure on what to groupby and which column to pivot on. I have two input columns
column1 - varchar(255)
column2-integer - constant value 1
and the output should have 11 columns
You can concatenate the content of all rows beginning from Line 11 before sending them downstream. You have to set your transformer to sequential mode because otherwise your data-rows will be processed on different nodes.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
OK, hold on... according to what you posted that's exactly what you need to do.
So are you saying that the entire file should be converted into a single record?spemma123 wrote:I am not sure if vertical pivot works in this case because from 11th row till end of the file the data should go into one column.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
No, here is what i need to get ....
INPUT
------------
column1
INSERT
20120825
20120905
1
X58065
OT
RP
N
INSERT Check
7842504 7842504
7841631 7841631
7840441 7840441
7840768 7840768
2884402 2884402
4909301 4909301
4933665 4933665
4854045 4854045
2243645 2243645
7286322 7286322
2851545 2851545
2203371 2203371
OUTPUT
------------
column1 column2 column3 column4 column5 column6 column7 column8 column9 column10 column11
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7842504 7842504
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7841631 7841631
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7840441 7840441
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7840768 7840768
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 2884402 2884402
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 4909301 4909301
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 4933665 4933665
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 4854045 4854045
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 2243645 2243645
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7286322 7286322
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7286322 7286322
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 2851545 2851545
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 2203371 2203371
INPUT
------------
column1
INSERT
20120825
20120905
1
X58065
OT
RP
N
INSERT Check
7842504 7842504
7841631 7841631
7840441 7840441
7840768 7840768
2884402 2884402
4909301 4909301
4933665 4933665
4854045 4854045
2243645 2243645
7286322 7286322
2851545 2851545
2203371 2203371
OUTPUT
------------
column1 column2 column3 column4 column5 column6 column7 column8 column9 column10 column11
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7842504 7842504
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7841631 7841631
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7840441 7840441
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7840768 7840768
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 2884402 2884402
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 4909301 4909301
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 4933665 4933665
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 4854045 4854045
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 2243645 2243645
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7286322 7286322
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 7286322 7286322
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 2851545 2851545
INSERT 20120825 20120905 1 X58065 OT RP N INSERT Check 2203371 2203371
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
So, you want the first nine rows to appear in every row of output then, from row #10 onwards, the two input columns to appear in column10 and column11 ? Is this a correct specification?
What is your target?
To get this right you probably need to run in sequential mode. For 50,000 rows that should not be too much of a problem.
What is your target?
To get this right you probably need to run in sequential mode. For 50,000 rows that should not be too much of a problem.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Yes you are right my target is oracle table. should i use vertical pivot ? if yes what should be my groupby and pivot ?ray.wurlod wrote:So, you want the first nine rows to appear in every row of output then, from row #10 onwards, the two input columns to appear in column10 and column11 ? Is this a correct specification?
What is your target?
To get this right you probably need to run in sequential mode. For 50,000 rows that should not be too much of a problem.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This is not a pivot problem.
This is a "remember the first nine rows in stage variables and use them to populate column1 through column9 while reading all subsequent input lines" problem.
Populate the stage variables conditionally on the value of @INROWNUM.
Run on a single node or in sequential mode.
This is a "remember the first nine rows in stage variables and use them to populate column1 through column9 while reading all subsequent input lines" problem.
Populate the stage variables conditionally on the value of @INROWNUM.
Run on a single node or in sequential mode.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi Spemma,
Can the input data be split into two? first nine rows and rest all.
Add a 'Dummy' column with constant value, to the first part and 'Dummy' column with the same value in the second part and join the two..
Someone can suggest on how to get all the rows to one column (pivoting stuff) ..
Can the input data be split into two? first nine rows and rest all.
Add a 'Dummy' column with constant value, to the first part and 'Dummy' column with the same value in the second part and join the two..
Someone can suggest on how to get all the rows to one column (pivoting stuff) ..
Last edited by Harini on Tue Jan 22, 2013 5:25 am, edited 1 time in total.