CODEWITHSUNDEEP
sqlteradata
nikhila reddy
nikhila reddy

Reputation: 63

teradata concatenating rows based on a column

I have a table like below

Cstmr_ID | PPM | prepaid |home_phone|srvc_num
1        |Y    |N        |N         |0422222222
1        |Y    |N        |N         |0433333333
1        |N    |N        |Y         |0333333333 
2        |y    |N        |N         |0455555555
2        |N    |N        |Y         |0355555555

Now I want a table like this.

Cstmr_id  | PPM_srvc_num         |prepaid_srvc_num|home_phone_srvc_num
1         |0422222222,0433333333 |                |0333333333
2         |0455555555            |                |0355555555

Upvotes: 0

Views: 706

Answers (1)

Diego
Diego

Reputation: 832

What you want to do is to transpose your table and then group concatenate records. Since Teradata 14.10 there is TD_UNPIVOT function to transpose but I have not worked with that yet.

If you can live with fixed upper limit (N) on numbers the same column for one Cstmr_id in your result you can use case statements and row_number() to emulate transpose and group concatenate.

select Cstmr_id
, max(case when PPM='Y' and rn=1 then srvc_num else null end) 
||  coalesce(max(case when PPM='Y' and rn=2 then ','||srvc_num else null end),'')
...
||  coalesce(max(case when PPM='Y' and rn=N then ','||srvc_num else null end),'')
as PPM_srvc_num
-- add the further lines for prepaid ='Y' and home_phone='Y'
from (
  select *
  , row_number() over (partition by Cstmr_ID, PPM, prepaid, home_phone) as rn 
  from your_table 
) dt
group by 1

I have not tested the code above but I have actually done that type of transformation before for myself so it should work after writing it out completely for your actuall table

Upvotes: 0

Related Questions