CODEWITHSUNDEEP
sqlsql-serversql-server-2008
R Kumar
R Kumar

Reputation: 137

Select multiple rows in single result using SQL Server 2008

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.

Upvotes: 1

Views: 109

Answers (3)

Wheater
Wheater

Reputation: 58

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

Upvotes: 0

Ted Elliott
Ted Elliott

Reputation: 3493

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

Upvotes: 0

Greg Viers
Greg Viers

Reputation: 3523

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

Upvotes: 2

Related Questions