All Forums General
gnanareddy 19 posts Joined 03/14
30 Jun 2014
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.

dnoeth 4628 posts Joined 11/04
03 Jul 2014

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.

Dieter

Purushotham 87 posts Joined 04/14
07 Jul 2014

Hi dnoeth,

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

Regards,
Purushotham.

Purushotham.M

gnanareddy 19 posts Joined 03/14
08 Jul 2014

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.

You must sign in to leave a comment.