CODEWITHSUNDEEP
sqloracle-database
Varun Rao
Varun Rao

Reputation: 801

Oracle SQL row values as columns

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

Upvotes: 0

Views: 71

Answers (1)

ughai
ughai

Reputation: 9880

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)

Upvotes: 2

Related Questions