Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

General

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

9 REPLIES

Re: Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

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

Re: Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

Hi raja,

I am using 13.0, can we use Case statement?

Re: Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

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 ....

Re: Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

Hi Raja,

Now I got the result what i expect. I appreciate a lot.

Thank u once again...

Regards,

Purushotham.

Re: Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

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.....

Re: Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

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

Re: Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

Hi Raja,

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

Regards,
Purushotham.

Re: Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

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.

Re: Could anyone please tell me how to transpose from column to rows using CASE statement in teradata

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.