CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Karim .El eleimy
Karim .El eleimy

Reputation: 1

selecting values from multiple records in one row but different columns in SQL

I have a table as follows

  Ident      type      id   value
   A         CP        1    1000
   A         CP        2    EXIST
   A         GVI       1    100
   A         GVI       2    NOT EXIST

I need to view them as follows

  Ident   type   value( id=1)  value(ld=2)
    A     CP     1000          Exist
    A     GVI    100           NOT EXIST

Any idea how to do it?

Upvotes: 0

Views: 1018

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

Use Conditional Aggregate

select Ident, 
       type,
       max(case when id=1 then value end) as [value(ld=1)],
       max(case when id=2 then value end) as [value(ld=2)]
from yourtable
group by Ident,type

or You can also use Pivot operator

SELECT ident, 
       type, 
       [1] AS [value(ld=1)], 
       [2] AS [value(ld=2)] 
FROM   (SELECT * 
        FROM   yourtable) A 
       PIVOT (Max(value) 
             FOR id IN ([1], 
                        [2])) pv

Upvotes: 2

Related Questions