Fetching Columns Of A Multiple Rows In One Row

I have a DB table with records as shown below, ID ATTR_NAME ATTR_VALUE 1 ABC DEF 1 GHI JKL 1 MNO PQR I would like to get a single row as

Solution 1:

It may be a little fragile and not that future proofed, but Pivot can give you what you want:

SELECT*FROM (
   SELECT attr_name, attr_value
   FROM   test
)
PIVOT
(  MIN(attr_value)
   FOR attr_name IN ( 'ABC','GHI','MNO' )
)

However, I'd advise that you consider if you really need it in that format and see if you can get it out in a more natural format.

Solution 2:

select ID, 
   sum(casewhen attr_name ='ABC'then attr_value end) as ABC, 
   sum(casewhen attr_name ='GHI'then attr_value end) as GHI, 
   sum(casewhen attr_name ='MNO'then attr_value end) as MNO
from DB
groupby ID;