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

possible duplicate of [Oracle SQL pivot query](http://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — sstan, Jun 05 '15 at 05:58

score 2 · Accepted Answer · answered Jun 05 '15 at 05:59

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)

1 Answers1