Please suggest

[G SHIVARANJANI]

Hi,


I have some records in this manner,

A,AT1,GT1
A,AT1,GT2
A,AT2,GT1
A,AT2,GT2
A,AT3,GT1
A,AT3,GT2

OR

A,AT1,GT1
A,AT1,GT2
A,AT1,GT3
A,AT2,GT1
A,AT2,GT2
A,AT2,GT3




And i want the resultant recordS as

A,AT1,AT2,AT3,GT1,GT2 and

A,AT1,AT2,GT1,GT2,GT3


Please suggest.

[ray.wurlod]

Search the forum for "vertical pivot", which is what you are trying to do. There are several ways to effect this technique.

[G SHIVARANJANI]

I could see that this can be achieved by using transformer, but this goes fine with two columns ..

its going complex with the three column data...as shown

Search the forum for "vertical pivot", which is what you are trying to do. There are several ways to effect this technique. ...

[Maveric]

Are u mapping the the data to different fields after the pivot. like GT1,GT2,GT3 to F1, F2, F3? If so u can use an aggregator stage to get the record count. Then in transformer use a variable (counter) and count(field from aggregator) to loop and concatenate all the GT1 etc with a string separator to one stage variable and when the counter is equal to count pass the record. in the field derivation use field function to get the values. Sorting and partitioning needs to be taken care of and also the logic might need some tweaking.

[shivadas]

Hi,


I have some records in this manner,

A,AT1,GT1
A,AT1,GT2
A,AT2,GT1
A,AT2,GT2
A,AT3,GT1
A,AT3,GT2

OR

A,AT1,GT1
A,AT1,GT2
A,AT1,GT3
A,AT2,GT1
A,AT2,GT2
A,AT2,GT3




And i want the resultant recordS as

A,AT1,AT2,AT3,GT1,GT2 and

A,AT1,AT2,GT1,GT2,GT3


Please suggest.

write this in user defined sql
----------------------------------
select distinct a from xyz
union
select distinct b from xyz
union
select distinct c from xyz


Try this and let me know if it is working
shiva

[pbatchu]

Try this

SELECT distinct col1, col2, col3,col4
from tab1

[DSguru2B]

I could see that this can be achieved by using transformer, but this goes fine with two columns ..

its going complex with the three column data...as shown

Make the first two columns as a single column, as in while doing the check within the transformer, concatenate the first two columns to create a single column and do the same as if there were only two columns.