I have below table.
code type value
===================
100 R 3300
100 B 7900
101 R 6800
100 D 2100
100 C 2300
101 C 1200
I want the select statement return below result when I select for code=100.
code Rvalue Bvalue Dvalue Cvalue
==================================
100 3300 7900 2100 2300
I was successful achieving this using inline queries. But I want to know if there is a better way to do it.
Thanks in Advance.
Not sure if this is the best way, but it works. Use max() and CASE to create multiple columns from one. Max will always choose the real value instead of the NULL. You could substitute 0 or another default if you want.
SELECT code
,max(CASE
WHEN type = 'r'
THEN value
ELSE NULL
END) RValue
,max(CASE
WHEN type = 'b'
THEN value
ELSE NULL
END) BValue
,max(CASE
WHEN type = 'd'
THEN value
ELSE NULL
END) DValue
,max(CASE
WHEN type = 'c'
THEN value
ELSE NULL
END) CValue
FROM mytable
GROUP BY code
ELSE NULL is not required BTW as that is the behaviour anyway.
You could use the PIVOT operator. I've never actually used it myself, but I think it will do what you're trying to do. Not really sure it's that much better though.
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Use PIVOT:
SELECT *
FROM
(
<table that gives you current results>
) src
PIVOT
(
MAX(value) --must use an aggregrate function here
FOR type IN('r', 'b', 'd', 'c')
) pvt
