All Forums Analytics
chill3che 99 posts Joined 10/12
20 Jan 2014
Transpose rows to columns

Greetings Experts,
Can you help me in the following scenario with the SQL without UDF's.
input:
sales_month    sales_amnt
jan                    100
feb                    200
mar                   300
apr                    400
may                   500
....                      ....
dec                   1200
 
Desired output:
jan         feb         mar           apr          may       .........       dec
100        200        300           400          500                       1200
 
Thank you for your time on this.
 

Thanks, Cheeli
dnoeth 4628 posts Joined 11/04
20 Jan 2014

Hi Cheeli,

select
   min(case when sales_month = 'jan' then sales_amnt else 0 end) as jan,
   min(case when sales_month = 'feb' then sales_amnt else 0 end) as feb,
...
   min(case when sales_month = 'dec' then sales_amnt else 0 end) as "dec"
from tab

 

Dieter

YouriD 9 posts Joined 01/14
23 Jan 2014

Cheeli
In case you might have multiple entries per month in your table or values that can be greater or smaller than zero at the same time you can take Dieters code and replace the "min" into "sum"
regards
Youri

Jomy86 1 post Joined 05/16
26 May 2016

how to convert columns to rows without UNION ALL function?? Dow we have any UDF for that??
please help.
Thanks in Advance.

dnoeth 4628 posts Joined 11/04
26 May 2016

Since TD14.10 there's a TD_UNPIVOT function:
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/SQL_Reference/B035_1145_112A/Table_Operators.098.21.html

Dieter

You must sign in to leave a comment.