All Forums General
Purushotham 87 posts Joined 04/14
04 Jul 2014
Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

Hi Everyone,
I have customer table,having columns as Cust_id,Type_contact,contact.
Eg: Cust_id      type_contact         contact
        10            Email                  puru@wipro.com
        10            Mobile                 9902514139
        10            Telephone            080-2434136
 
But I need output as :cust_id     type_contact                  contact
                               10         Email-mobile-telephone      puru@wipro.com-9902514139-080-2434136
 
Could anyone please tell me how to acheive this by using CASE statement.I dont need With Recursive .
 
Regards,
Purushotham
 

Purushotham.M
Raja_KT 1246 posts Joined 07/09
04 Jul 2014

Which version you are in?You can use tdstats.udfconcat . 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Purushotham 87 posts Joined 04/14
04 Jul 2014

Hi raja,

I am using 13.0, can we use Case statement?

Purushotham.M

Raja_KT 1246 posts Joined 07/09
04 Jul 2014

Can you try something thus:
select id,
max (case rn when 1 then tcontact else null end) ||'-'||
max (case rn when 2 then tcontact else null end) ||'-'||
max (case rn when 3 then tcontact else null end),
max (case rn when 1 then contact else null end) ||'-'||
max (case rn when 2 then tcontact else null end) ||'-'||
max (case rn when 3 then tcontact else null end) from
(select id,tcontact,contact,row_number() over( order by tcontact..) as rn from db.test1 ) bid group by 1 order by ....

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Purushotham 87 posts Joined 04/14
05 Jul 2014

Hi Raja,
Now I got the result what i expect. I appreciate a lot.
Thank u once again...
 
Regards,
Purushotham.
 

Purushotham.M

Purushotham 87 posts Joined 04/14
06 Jul 2014

Hi Raja,
This is the final question about transpose rows to columns.
I have a table with columns as
cust_id contact_type1 contact_type2  contact_type3  contact1           contact2   contact3
100        email               mobile             telephone    puru@wipro.com  99666778   6666564
But I need output as: cust_id   contact_type      contact
                                100        email               puru@wipro.com
                                 100       mobile               990901641
                                 100       telephone          6666564
 
Could you please raja ,pls help me out by sharing solution to my question..
Thanks in advance.....
 

Purushotham.M

Raja_KT 1246 posts Joined 07/09
07 Jul 2014
How about?

select cust_id,contact_type1 as contact,contact1  from TEST_123 
union all
select cust_id,contact_type2 as contact ,contact2  from TEST_123 
union all
select cust_id,contact_type3 as contact,contact3  from TEST_123 

 
 
 
 
 
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Purushotham 87 posts Joined 04/14
07 Jul 2014

Hi Raja,

Thank you so much.But I need using CASE Statement.

Regards,
Purushotham.

Purushotham.M

Raja_KT 1246 posts Joined 07/09
07 Jul 2014

Why CASE?Case is built on business logic.
Is this what you are thinking of  that if contact has @ then type of contact is  email, if contact has something like >=9 digits then mobile, if contact has <9 digits then telephone? or somethign else?
You can filter with "case when " from your data available for each field. Once that is done you can use union all.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Purushotham 87 posts Joined 04/14
07 Jul 2014

Hi Raja,

Yes I am thinking abt what you speficied in the above.

Thank you for sharing solution and suggestions for my queries.

Regards,
Purushotham.

Purushotham.M

You must sign in to leave a comment.