Pivoting a dynamic column based on split value

I Have a couple of tables as per below

Position table

Id	PositionName
1	Developer
2	Analyst
3	Tester

Employee table

Id	Name	Positions
1	John	1,2
2	Lisa	3
3	Smith	1
4	Willow	NULL
5	Burly	2,3

From the above tables, what is the query to produce a pivoted report as per below?

Id	Name	Developer	Analyst	Tester
1	John	Y	Y	N
2	Lisa	N	N	Y
3	Smith	Y	N	N
4	Willow	N	N	N
5	Burly	N	Y	Y

I am stuck with the fact I have to do some split string and use the CASE WHEN to apply Y or N to the pivot.

here’s my playground in SQL fiddle http://sqlfiddle.com/#!18/2ad8d/31

Answer

I’m having trouble getting Fiddle to work right now, but the code isn’t too bad to just reproduce in full. Note that you should watch your @Cols variable to make sure all of your position names work as column names in real life, that’s often not the case! Also note that your original example had duplicate employee IDs, I gave them unique values.

CREATE  table Position (
  Id int,
  Name varchar(10)
);

insert into Position values
(1, 'Developer'),
(2, 'Analyist'),
(3, 'Tester');

CREATE table Employee (
  Id int,
  Name varchar(10),
  Position varchar(MAX)
);

insert into Employee values
(1, 'John', '1,3'),
(2, 'Lisa', '3'),
(3, 'Smith', '1'),
(4, 'Willow', NULL),
(5, 'Burly', '2,3');

--This is your basic working PIVOT, we'll implement as a dynamic query once we're satisfied it works
;with cteEmp as (
    SELECT E.Id as EID, E.Name as EName, P.Name as PName
        , CASE WHEN CHARINDEX(CONVERT(nvarchar(10), P.Id)
            , CONCAT(',', E.Position, ',') ) > 0 THEN 'Y' ELSE 'N' END as HasPos 
    FROM Employee as E CROSS JOIN Position as P
)SELECT Piv.* FROM cteEmp as E PIVOT (max(HasPos) FOR PName in (Developer, Analyist, Tester)) as Piv;


--To make it dynamic, build the list of positions from a query
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Name)
    FROM Position
    FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'with cteEmp as (
    SELECT E.Id as EID, E.Name as EName, P.Name as PName
        , CASE WHEN CHARINDEX(CONVERT(nvarchar(10), P.Id)
            --, CONCAT('','', E.Position, '','') ) > 0 --Not 2008R2!
            , '','' + E.Position + '','' ) > 0  
               THEN ''Y'' ELSE ''N'' END as HasPos 
    FROM Employee as E CROSS JOIN Position as P
)SELECT Piv.* FROM cteEmp as E PIVOT (max(HasPos) 
FOR PName in (' + @cols + ') ) as Piv;'

execute(@query)

EDIT: Fixed the missing close parenthesis in the dynamic query.

EDIT: Note: This does not use a split function, it takes advantage of the facts that the ID must be an integer and the positions listed are delimited in a predictable way. We don’t need a list of position IDs, we only need to know if the position ID in question is in the list. We prepend and append commas so we can search on “,1,” and not just “1” because “1” would also match “21” but “,1,” only matches the single ID.