0

Given table:

ID   NAME  VALUE 

1     A     N

1     B     Y   

1     C     N

I want the table in below format:

ID  A   B   C

1   N   Y   N
SHUBHAM KUMAR
  • 1

1 Answers1

3

You can use conditional aggregation

select id, 
       max(case when name='A' then value end) as A,
       max(case when name='B' then value end) as B,
       max(case when name='C' then value end) as C
from tablename
group by id
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • Note that this is a product specific answer to a question with no dbms specified. – jarlh Nov 26 '19 at 09:11
  • 1
    @jarlh, does conditional aggregation product specific? as far i know, it should work regardless of any product – Fahmi Nov 26 '19 at 09:12
  • Single quotes are for string literals, use double quotes for delimited identifiers, i.e. `as "A"`. (But I'd rather skip it, and simply do `as A`.) – jarlh Nov 26 '19 at 09:13
  • what if we have thousands of line, we can't use case for thousands value. Is there any other solution ? – SHUBHAM KUMAR Nov 26 '19 at 10:09
  • 1
    @SHUBHAMKUMAR . . . There is no problem with thousands of *rows* in the table. If you have thousands of *columns* in your desired result set, then many databases will not even support tables or result sets with so many columns. – Gordon Linoff Nov 26 '19 at 12:11