0

This isn't the first time I've been faced with this type of problem and I've still yet to figure it out.

I'm trying to pull the records of an employee and find which races they are. The way the data is returned currently is as:

Emp # | Race
-------------
1111  | White
1111  | Asian

I would like it to look like this in the end:

Emp # | White | Black | Hispanic | Asian | Indian | Other
---------------------------------------------------------
1111  |   Y   |   N   |    N     |   Y   |   N    |   N

I've attempted this through CTE's and the PIVOT function, but I may have just not had a great understanding of how the use PIVOT.

Ethan Orcutt
  • 7
  • 5
  • Holy bejesus that was too stupidly simple. I searched for a good long while and even read all the possible duplicates that SO recommended and still didn't find that one. Thank You! – Ethan Orcutt Apr 06 '17 at 13:47

1 Answers1

0

Build dynamic SQL like that

select Emp, 
   case when MAX(case when Race = 'White' then 1 else 0 end) = 1 then 'Y' else 'N' as White,
   case when MAX(case when Race = 'Asian' then 1 else 0 end) = 1 then 'Y' else 'N' as Asian,
   case when MAX(case when Race = 'Black ' then 1 else 0 end) = 1 then 'Y' else 'N' as Black
from [YourTable]
group by Emp
ventik
  • 877
  • 7
  • 18
  • 1
    Why the double case? I would go MAX(case when Race = 'White' then 'Y' else 'N' end) – John Cappelletti Apr 06 '17 at 13:50
  • Giorgos Betsos - I know that this is static. I mean that author should build dynamic query that produce query like this. He can produce it in pure sql or in his client application. – ventik Apr 06 '17 at 13:51
  • John Cappelletti - In this case it will work but if you decide to use different values instead of Y and N, there may be some issues. – ventik Apr 06 '17 at 13:53