How to convert each row into a column

spemma123 · Post by **spemma123** » Mon Jan 21, 2013 1:44 am

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 !

BI-RMA · Post by **BI-RMA** » Mon Jan 21, 2013 2:16 am

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]).

prasson_ibm · Post by **prasson_ibm** » Mon Jan 21, 2013 3:24 am

Hi,
Column that need to be converted are static or dynammic?

spemma123 · Post by **spemma123** » Mon Jan 21, 2013 8:13 am

They are dynamic except for the 1st and 10th row values

spemma123 · Post by **spemma123** » Mon Jan 21, 2013 9:15 am

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

BI-RMA · Post by **BI-RMA** » Mon Jan 21, 2013 10:46 am

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.

spemma123 · Post by **spemma123** » Mon Jan 21, 2013 11:13 am

I have 50000 rows after line 11....i think it is not a god idea to concatenate all those.

chulett · Post by **chulett** » Mon Jan 21, 2013 11:32 am

OK, hold on... according to what you posted that's exactly what you need to do.

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.

So are you saying that the entire file should be converted into a single record?

spemma123 · Post by **spemma123** » Mon Jan 21, 2013 1:45 pm

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

ray.wurlod · Post by **ray.wurlod** » Mon Jan 21, 2013 4:14 pm

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.

chulett · Post by **chulett** » Mon Jan 21, 2013 5:47 pm

That would have been awesome information to include in your first post, don't you think?

spemma123 · Post by **spemma123** » Mon Jan 21, 2013 6:55 pm

chulett wrote:That would have been awesome information to include in your first post, don't you think?

I should have...sometimes things slip away..
but still not clear on the solution can you please briefly provide the idea if you don't mind.

Thanks.

spemma123 · Post by **spemma123** » Mon Jan 21, 2013 6:56 pm

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.

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 · Post by **ray.wurlod** » Mon Jan 21, 2013 7:40 pm

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.

Harini · Post by **Harini** » Tue Jan 22, 2013 3:45 am

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) ..