All Forums Database
praneethbobba9 5 posts Joined 05/12
15 Mar 2013
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.

dnoeth 4628 posts Joined 11/04
17 Mar 2013

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

Dieter

himuengg 5 posts Joined 05/12
23 Mar 2013

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

shavyani 23 posts Joined 03/15
02 Apr 2015

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

dnoeth 4628 posts Joined 11/04
06 Apr 2015

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

Dieter

You must sign in to leave a comment.