Teradata SQL to transpose columns to rows

General
Enthusiast

Teradata SQL to transpose columns to rows

Dear All,

I would like to transpose the data columns to rows from a teradata table.

sample data:

id   date      d1   d2    d3

11 1jan12  111 222     

22 2jan13          33     44

21 1mar14  121         131

Note: First two columns are same, next d1, d2 and d3 columns need to be converted to rows, in case null need to ignore.

Expexted format:

id    date      d

11 1jan12  111

11 1jan12  222

22 2jan13   33

22 2jan13   44

21 1mar14  121

21 1mar14  131

====================

could any body help me with SQL query to achieve this output.

Thank you in advance.

Regards,

Gnana Reddy.

3 REPLIES
Junior Contributor

Re: Teradata SQL to transpose columns to rows

This has been answered multiple times...

select id, date, d1
from tab
where d1 is not null

union all

select id, date, d2
from tab
where d2 is not null

union all

select id, date, d3
from tab
where d3 is not null

Or do a cross join to a table with three rows in it and use a CASE to pick the correct column. More complicated to code but more efficient.

Enthusiast

Re: Teradata SQL to transpose columns to rows

Hi dnoeth,

Could you please share me above scenarie by using CASE statement.

Regards,
Purushotham.
Enthusiast

Re: Teradata SQL to transpose columns to rows

Thank You very much for your reply, This way I did, Since that is taking long time on billions of rows I requested for any possible ways to write cost effectively.

Regards,

Gnana Reddy.