Oracle SQL row values as columns

Question

I have a Table (TableA) with 3 columns (ColA,ColB,ColC)

ColA and ColB are Primary Keys

select *
from TableA
where ColA = '001';

gives me this

COLA COLB      COLC 
---- ----- -------- 
001  AA1        460  
001  AB1        380 
001  AC1        950  
001  AD1       null 
001  AE1       null

I need this result in this format

COLA COLC-AA1 COLC-AB1 COLC-AC1 COLC-AD1 COLC-AE1
---- -------- -------- -------- -------- --------
001       460      380      950     null     null

i.e all resulting rows into one row with values of ColC under ColB value ColumnNames

ughai · Accepted Answer · 2015-06-05 05:59:01Z

2

You can use PIVOT like this. SQL Fiddle

Query

SELECT COLA,"'AA1'","'AB1'","'AC1'","'AD1'","'AE1'"
FROM tbl1
PIVOT(MAX(COLC) for COLB IN('AA1','AB1','AC1','AD1','AE1'))

OUTPUT

COLA    'AA1'   'AB1'   'AC1'   'AD1'   'AE1'
001 460 380 950 (null)  (null)

answered Jun 5, 2015 at 5:59

ughai

9,9004 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1