How to convert columns into Rows with metadata?

pkll · Post by **pkll** » Wed Jun 11, 2014 6:43 am

Hi,

I have One Scenario.

EMPNO|ENAME|SALARY
101|AAA|10000
102|BBB|20000
103|CCC|30000

I want Output

EMPNO|101|102|103
EMPNAME|AAA|BBB|CCC
SALARY|10000|20000|30000

I tried but I didn't get desired output. I create one dummy key column in transformer datastage and hardcoded one value. In sort stage based on dummy key column I selected create keychange column = TRUE.
Next by using the transformer stage I checked duplicated value or not if duplicated value I concatenate the value and remove duplicate stage I had taken duplicate retain = Last.

Finally I got output is like

EMPNO ENAME SAL
-------------- ------------------ --------------------------
101|102|103 AAA|BBB|CCC 10000|20000|30000

But I want Output

EMPNO|101|102|103
EMPNAME|AAA|BBB|CCC
SALARY|10000|20000|30000

Please help me ?

chulett · Post by **chulett** » Wed Jun 11, 2014 7:01 am

So, which part isn't working - the sort, the transformer or the remove duplicates? Seems to me you'll need to hardcode all three values, pivot then the concatenation and finally remove duplicates.

pkll · Post by **pkll** » Wed Jun 11, 2014 7:07 am

Hi Chullet,

in the above example I don't have any key column so I had created one dummy column and assign value '1'. I had taken that value as key column.
Remaining all I applied logic.

but I am getting output as

EMPNO ENAME SAL
-------------- ------------------ --------------------------
101|102|103 AAA|BBB|CCC 10000|20000|30000

But I Want output

EMPNO|101|102|103
EMPNAME|AAA|BBB|CCC
SALARY|10000|20000|30000

chulett · Post by **chulett** » Wed Jun 11, 2014 7:15 am

pkll · Post by **pkll** » Wed Jun 11, 2014 7:21 am

Hi Chulett,

I have not three columns. I have around 50 columns .I can't create 50 dummy columns.

EMPNO
-------
101
102
103

Required output is

EMPNO|101|102|103

but I am getting output as
EMPNO
101|102|103

So how can I get required output means I need to concatenate with metadata also.

chulett · Post by **chulett** » Wed Jun 11, 2014 7:25 am

Awesome. Another "example" with made up data that doesn't match what really needs to be done. Would it have killed you to have mentioned that in the original post? And repeating everything with just one column as the example doesn't help nor change anything, my answer is still my answer.

First explain to me why you "can't create 50 dummy columns". Can't or just don't really want to?

pkll · Post by **pkll** » Wed Jun 11, 2014 7:30 am

If I will create 50 Dummy columns performance will decrease more over I have more than 2 million records is there. Above I had taken one column instead of three columns .if 1 column output will come means I can apply same logic remaining columns.
any how
based on my output how can I get required output

my output is
EMPNO
----------
101|102|103

Required output is

EMPNO|101|102|103

chulett · Post by **chulett** » Wed Jun 11, 2014 7:45 am

Performance will decrease? So? Isn't the goal to get a working solution? I've already posted how you can get your desired output, 1 column or 50. And I really don't see the addition of a number of columns as any kind of performance killer. And please stop repeating your example. Repetition != helping.

Look - there's no automagic way to get the metadata in the job as data unless you provide it. I don't really see any way around the need for additional columns and I've given you the traditional answer where they are hardcoded into those additional columns. Another option you could look into to provide that would be to put the column names as data in another source - flat file, database table - and then merge the two inputs. That could be a cross join or it could be a merge depending on how you setup the column name values. I'll leave you to work out the gory details.

And by that I mean chose an approach, give it a shot and see what works and what doesn't and then come back with those issues.

chulett · Post by **chulett** » Wed Jun 11, 2014 1:40 pm

Question for the masses - will the Pivot stage support listing ~100 column names in the fields in question? Or does it not really care how many columns are listed as long as they all fit within that property / string?

ray.wurlod · Post by **ray.wurlod** » Wed Jun 11, 2014 5:03 pm

pkll wrote:If I will create 50 Dummy columns performance will decrease

Prove it.

pkll · Post by **pkll** » Thu Jun 12, 2014 4:10 am

Hi Chulett,

As per your suggestion I got the required output. If I tortured you extremely sorry. As per our conversation I am learning something.
Thanks for it.

I have one more doubt, Now I am thinking as reverse order

suppose if my input is

EMPNO|101|102|103
EMPNAME|AAA|BBB|CCC
SALARY|10000|20000|30000

shell I get output as

EMPNO|ENAME|SALARY
101|AAA|10000
102|BBB|20000
103|CCC|30000

is it possible? if possible please suggest me?

Thanks for Advance!!!

chulett · Post by **chulett** » Thu Jun 12, 2014 6:50 am

pkll wrote:If I tortured you extremely sorry. As per our conversation I am learning something. Thanks for it.

No worries... and good.

I'll have to ponder the reverse though it is 'just another pivot'... and there's no reason for me to go this alone, other suggestions welcome!

pkll · Post by **pkll** » Fri Jun 13, 2014 12:30 am

Hi Chulett,

Can you please explain detail level how can I do by using pivot stage?

Thanks for Advance!!

chulett · Post by **chulett** » Fri Jun 13, 2014 8:51 am

I don't have much if any spare time at the moment. Hopefully you've been working on this, pondering / testing / something and not simply waiting for me... right?