SQL Pivot IF EXISTS

Question

I think I need a PIVOT but I'm yet to find an example that uses only 1 column.
Current data

CustID       Value                
1              A                   
1              B
1              C                  
2              A                   
3              B                  
4              A
4              C

I need

StudentNumber      A      B     C
1                  Yes   Yes  Yes
2                  Yes   No    No
3                  No    Yes   No
4                  Yes   No    Yes

I appreciate it's probably a very simple query but I really am struggling!

score 1 · Accepted Answer · answered Nov 18 '14 at 10:30

You can use a case statement in the column list checking for null to get the value you need.

select P.CustID,
       case when P.A is null then 'No' else 'Yes' end as A,
       case when P.B is null then 'No' else 'Yes' end as B,
       case when P.C is null then 'No' else 'Yes' end as C
from YourTable as T
pivot (min(T.Value) for T.Value in (A,B,C)) as P

Or you can use a derived table and isnull in the column list for No values.

select P.CustID,
       isnull(P.A, 'No') as A,
       isnull(P.B, 'No') as B,
       isnull(P.C, 'No') as C
from (
     select CustID,
            Value,
            'Yes' as S
     from YourTable
     ) as T
pivot (min(T.S) for T.Value in (A,B,C)) as P

score 0 · Answer 2 · answered Nov 18 '14 at 10:23

0

Try this.

SELECT custid,
       Max(CASE
             WHEN value = 'a' THEN 'YES' else 'NO'
           END) A,
       Max(CASE
             WHEN value = 'b' THEN 'YES' else 'NO'
           END) B,
       Max(CASE
             WHEN value = 'c' THEN 'YES' Else 'NO'
           END) C
FROM   <tablename>
GROUP  BY CustID

answered Nov 18 '14 at 10:23

Pரதீப்

91,748
19
131
172

can you try it in pivot format..using max case we can do it either way. – mohan111 Nov 18 '14 at 10:28
It was that simple! Thanks very much Pradeep – Clummzie Nov 18 '14 at 10:34

SQL Pivot IF EXISTS

2 Answers2

Linked