How to convert all columns value to rows and then check for a particular column?

Question

i have a table like this:

student | group
1       | A
2       | B
1       | B
3       | C

i want to produce the output like the following:

Student | Group_A | Group_B | Group_C
1       | Yes     | Yes     | 
2       |         | Yes     | 
3       |         |         | Yes

Do anybody have any idea how can I produce this type of report? I tried several ways using Pivot and Unpivot but it's not working here

it's dynamic. but when the query will be executed, it will give output based on the table's that column value — adarksun, Oct 22 '14 at 21:52

score 1 · Accepted Answer · edited May 23 '17 at 12:20

I believe you are looking for pivot.

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

declare @students table(id int, type_ nvarchar)

insert into @students
values(1, 'A'),
(2, 'B'),
(3, 'C'),
(1, 'B');


select result.id Student,
    iif(result.A = 1, 'Yes', 'No') Group_A,
    iif(result.B = 1, 'Yes', 'No') Group_B,
    iif(result.C = 1, 'Yes', 'No') Group_C
from (
    select *
    from @students a
    pivot(count(type_) for type_ in (
                [A],
                [B],
                [C]
                )) as pivotExample
    ) result;

If a dynamic pivot is required, I would refer you to this:

SQL Server dynamic PIVOT query?

That's the solution I was looking for. Thanks – adarksun Oct 23 '14 at 05:51 — adarksun, Oct 23 '14 at 05:51

How to convert all columns value to rows and then check for a particular column?

1 Answers1