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.
Please suggest
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
Please suggest
Shivaranjani
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 137
- Joined: Sun Jan 07, 2007 11:17 pm
- Location: VISAKHAPATNAM
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
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. ...
Shivaranjani
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
write this in user defined sqlG 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.
----------------------------------
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
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.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
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.