Page 1 of 1
Please suggest
Posted: Sun Jul 22, 2007 10:31 pm
by 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.
Posted: Sun Jul 22, 2007 11:29 pm
by ray.wurlod
Search the forum for "vertical pivot", which is what you are trying to do. There are several ways to effect this technique.
Posted: Mon Jul 23, 2007 4:08 am
by 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
ray.wurlod wrote:Search the forum for "vertical pivot", which is what you are trying to do. There are several ways to effect this technique. ...
Posted: Mon Jul 23, 2007 6:25 am
by 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.
Re: Please suggest
Posted: Fri Jul 27, 2007 4:38 am
by shivadas
G SHIVARANJANI wrote: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
Posted: Thu Aug 09, 2007 4:43 pm
by pbatchu
Try this
SELECT distinct col1, col2, col3,col4
from tab1
Posted: Thu Aug 09, 2007 5:06 pm
by DSguru2B
G SHIVARANJANI wrote: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.