Transpose rows to column

General
Enthusiast

Transpose rows to column

Hi,

 

I need a solution to transpose rows into columns in a teradata table. I have sample input data like these:

 

IDNameSubjectMarks
1SachinPhysics90
2AnilPhysics80
3VimalPhysics85
1SachinChemistry80
2AnilChemistry85
3VimalChemistry86
1SachinMaths80
2AnilMaths85
3VimalMaths90

 

And I want my output data to be like below:

 

IDNamePhysicsChemistryMaths
1Sachin908080
2Anil808585
3Vimal8586

90

 

How can I achieve this?

Thanks in advance.

6 REPLIES
Junior Contributor

Re: Transpose rows to column

TD16 added PIVOT, if you're on an earlier release the manual entry for PIVOT also shows how to get the same result using MAX(CASE)

Enthusiast

Re: Transpose rows to column

Thanks for the reply.. Can you please show me one example using CASE?

Junior Contributor

Re: Transpose rows to column

Seems like the HTML-manuals are screwed up, clicking the Example link leads to UDT Expressions :-) 

Try this direct link  (or look at the PDF manuals)

Fan

Transpose rows to column

Hi can any once guide how to display date as column header as mention bewlow

 

sel  a.Shipping_Point_Cd,a.lob_desc,
           case  when b.Display_Order='1' then trim(cnt) else '' end as "Day_1",
           case  when b.Display_Order='2' then trim(cnt) else ' ' end as "Day_2",
           case  when b.Display_Order='3' then trim(cnt) else ' ' end as "Day_3",
           case  when b.Display_Order='4' then trim(cnt) else ' ' end as "Day_4",
           case  when b.Display_Order='5' then trim(cnt) else ' ' end as "Day_5",
           case  when b.Display_Order='6' then trim(cnt) else ' ' end as "Day_6"
           from abc a inner join
           (SELECT fiscal_dt as Material_Avail_Dt,RANK() OVER (PARTITION BY 1 ORDER BY fiscal_dt) as Display_Order
           FROM xyz WHERE ROLLING_DAY IN (0,1,2,3,4,5) AND type_cd ='INTRADAY' group by 1) b
           ON (a.Material_Avail_Dt=b.Material_Avail_Dt)
           order by a.lob_desc,a.Shipping_Point_Cd;

 

inner query result :

SELECT fiscal_dt as Material_Avail_Dt,RANK() OVER (PARTITION BY 1 ORDER BY fiscal_dt) as Display_Order
           FROM xyz WHERE ROLLING_DAY IN (0,1,2,3,4,5) AND type_cd ='INTRADAY' group by 1
               
 Material_Avail_Dt  Display_Order
-----------------  -------------
       2018-10-24              1
       2018-10-25              2
       2018-10-26              3
       2018-10-27              4
       2018-10-28              5
       2018-10-29              6

 

Expected  output:         

Shipping_Point_Cd  lob_desc        2018-10-24  2018-10-25        2018-10-26       2018-10-27        2018-10-28        2018-10-29
-----------------  --------------  -----------------  -----------  -----------  -----------  -----------  -----------
8206               ACCY            2
8402               ACCY            9
8206               IPAD                    1
BAU2               IPHONE          1713

 

Here Material_Avail_Dt values to display as column names.. Material_Avail_Dt will be changeing based on data.

 

Junior Contributor

Re: Transpose rows to column

For dynamically created column names you need Dynamic SQL, either a Stored Procedure or a script creating a view, etc.

 

In the latest TD16.20 Feature Upgrade 1 there's a new option for PIVOT to retrieve the column names using a Select.

Fan

Re: Transpose rows to column

We have to place sql in python script to send email in tabluar format (SQL output).