I have 2 tables and i am joining them baesd on CID. For CID 123 i have 4 types of status, these 4 status i have to load into 4 columns as shown below in output.
Table 1:
———-
CID STARTTIME
123 8/19/09 01:11:32 AM
124 8/19/09 01:13:13 AM
125 8/19/09 01:14:32 AM
126 8/19/09 01:15:36 AM
output
——-
CID STARTTIME status1 status2 status3 status4
123 8/19/09 01:11:32 AM abc abcd abcde abcdef
124 8/19/09 01:13:13 AM defg defgh – -
125 8/19/09 01:14:32 AM ghij ghijk ghijkl -
126 8/19/09 01:15:36 AM jklm – – –
Can you plz help me on this, i need to develop it in Informatica.
since the values in status column do not come from a predefined list, you would have to rank 4 rows per group (CID) and then transpose them using informatica or pl/sql or something like that. should be pretty simple. in informatica use variables to store values and compare CID to last CID… in PL/SQL … wait i don’t need pl/sql…. i can do this in one sql. performance will depend on the size of your dataset/table…
select CID,
max(case when stat_id = 1 then status else null end) as status_1,
max(case when stat_id = 2 then status else null end) as status_2,
max(case when stat_id = 3 then status else null end) as status_3,
max(case when stat_id = 4 then status else null end) as status_4
from
(
select CID, Status, rank () over(partition by CID, order by status) stat_id
from t2
)
group by CID
order by CID
Comments feed
can we do the following thru informatica?
Input:-
date col1 col2 col3
12 a 1 11
13 b 2 22
14 c 3 33
115 d 4 44
15 e 5 55
116 f 6 66
16 g 7 77
Output should be
12 13 14 115 15 116 16
a b c d e f g
1 2 3 4 5 6 7
11 22 33 44 55 66 77
read about normalizer transformation
Hi kirtan,
I have 2 tables and i am joining them baesd on CID. For CID 123 i have 4 types of status, these 4 status i have to load into 4 columns as shown below in output.
Table 1:
———-
CID STARTTIME
123 8/19/09 01:11:32 AM
124 8/19/09 01:13:13 AM
125 8/19/09 01:14:32 AM
126 8/19/09 01:15:36 AM
Table 2:
———–
CID status
123 abc
123 abcd
123 abcde
123 abcdef
124 efg
124 defgh
125 ghij
125 ghijk
125 ghijkl
126 jklm
output
——-
CID STARTTIME status1 status2 status3 status4
123 8/19/09 01:11:32 AM abc abcd abcde abcdef
124 8/19/09 01:13:13 AM defg defgh – -
125 8/19/09 01:14:32 AM ghij ghijk ghijkl -
126 8/19/09 01:15:36 AM jklm – – –
Can you plz help me on this, i need to develop it in Informatica.
Thanks in advance.
since the values in status column do not come from a predefined list, you would have to rank 4 rows per group (CID) and then transpose them using informatica or pl/sql or something like that. should be pretty simple. in informatica use variables to store values and compare CID to last CID… in PL/SQL … wait i don’t need pl/sql…. i can do this in one sql. performance will depend on the size of your dataset/table…
select CID,
max(case when stat_id = 1 then status else null end) as status_1,
max(case when stat_id = 2 then status else null end) as status_2,
max(case when stat_id = 3 then status else null end) as status_3,
max(case when stat_id = 4 then status else null end) as status_4
from
(
select CID, Status, rank () over(partition by CID, order by status) stat_id
from t2
)
group by CID
order by CID