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

Praneeth.

Tags (3)
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 Dfrom table agroup 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