Transpose rows to columns

Analytics
Enthusiast

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.

7 REPLIES
Senior Apprentice

Re: Transpose rows to columns

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
Enthusiast

Re: Transpose rows to columns

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

N/A

Re: Transpose rows to columns

how to convert columns to rows without UNION ALL function?? Dow we have any UDF for that??

please help.

Thanks in Advance.

Senior Apprentice

Re: Transpose rows to columns

Visitor

Re: Transpose rows to columns

helpful. Thank you. Amazing.

Enthusiast

Re: Transpose rows to columns

Please check the blog with detail steps.

 

http://transposeinteradata.blogspot.com/2017/01/transpose-in-teradata.html


This will be very useful in case you are looking for a SQL solution for transpose with huge data in the  table, where simple logic like "CASE WHEN.." blocks won't work, which may be perfect for few limited records.

 

 

Enthusiast

Re: Transpose rows to columns

TD_UNPIVOT function transforms table columns into rows. It is NOT for rows to columns.