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

Question

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?

Pரதீப் · Accepted Answer · 2015-08-23 14:37:40Z

2

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

edited Aug 23, 2015 at 14:37

answered Aug 23, 2015 at 14:29

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related