Select multiple rows in single result using SQL Server 2008

Question

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.

It's been done before here: http://stackoverflow.com/questions/12328849/query-to-display-output-horizontally — Chuck, Jul 02 '15 at 16:52

score 2 · Accepted Answer · answered Jul 02 '15 at 16:55

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

@vkp - No it doesn't. The `ELSE NULL` is not required BTW as that is the behaviour anyway. — Martin Smith, Jul 02 '15 at 17:03
@MartinSmith - true. But I find this more readable. And if OP wants to put a 0 in there instead it's a quick change. — Greg Viers, Jul 02 '15 at 17:23

score 0 · Answer 2 · answered Jul 02 '15 at 17:07

0

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

answered Jul 02 '15 at 17:07

Ted Elliott

3,415
1
27
30

score 0 · Answer 3 · answered Jul 02 '15 at 17:50

0

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

answered Jul 02 '15 at 17:50

Wheater

58
5

Select multiple rows in single result using SQL Server 2008

3 Answers3