Need to convert Rows to columns

Database
Enthusiast

Need to convert Rows to columns

Hi Guys


I'm facing a difficulty in achieving the below scenario

Scenario :

Table A is the table i'm having

( The column ID2 has only 4 distinct values -- A,B,C,D )

Table A
----------------|
ID1 ID2 Amt |
----------------|
1 A 45.00 |
2 B 66.00 |
2 A 33.00 |
3 C 34.00 |
3 A 100.00 |
4 D 10.00 |
4 B 101.00 |
---------------|


I need to achieve this

________________________________
ID A B C D |
-------------------------------|
1 45 |
2 33 66 |
3 100 3 |
4 101 10 |
-------------------------------|

A,B,C,D will be my columns and the amt corresponding to the ID will be under respective column

Please help

Thanks in advance 

Praneeth.

5 REPLIES
Junior Contributor

Re: Need to convert Rows to columns

Hi Praneeth,

select id1,
max(case when id2='A' then Amt end) as A,
max(case when id2='B' then Amt end) as B,
max(case when id2='C' then Amt end) as C,
max(case when id2='D' then Amt end) as D
from table a
group by 1;

You could also use three Outer Joins, but this is probably more efficient.

Dieter

Enthusiast

Re: Need to convert Rows to columns

A bit modified to get closer to the output shown by Praneet :-) . Although its based on Dieter's trick :-).

SEL ID1,

COALESCE((CASE WHEN ID2='A' THEN AMT END),'') AS A,

COALESCE((CASE WHEN ID2='B' THEN AMT END),'') AS B,

COALESCE((CASE WHEN ID2='C' THEN AMT END),'') AS C,

COALESCE((CASE WHEN ID2='D' THEN AMT END),'') AS D

FROM A

ORDER BY ID1

Enthusiast

Re: Need to convert Rows to columns

Hello guys,

I'm having a Table  which looks like

ID   AMT  MONTH

A    5          201501

A   10        201502

C    10        201501

The output should look like this:

ID  201501 201502

----------------------

A      5        10

and also there is a filter rolling_mth ='some value" (LIKE 2 OR 3...)so I l also have my rolling month being displayed and the final output would be like

The output should look like this:

ID  201501 201502 JAN-FEB  FEB-MAR    MAR-APR

--------------------------------------------------------------

A    5        10                10           5         15

I have teradata version 14,

In terms of explanation If there is anything I could add to this ?

 I would be glad for any solutions that would solve my issue.

Thanks

Junior Contributor

Re: Need to convert Rows to columns

Based on your example/result I don't get what you want, probably some SUM(CASE...) 

New Member

Re: Need to convert Rows to columns

select ID,

sum( coalesce( case when mth='201501' then amt end , 0)) as _201501,
sum(coalesce( case when mth='201502' then amt end, 0)) as _201502
from <TABLE>
group by ID