PIVOT -Dynamic

 select E.EmployeeGUID, CC.ChoiceCode AS ContactType, CM.ContactMethodValue INTO #ContactTypeAndValue

from contactmethod CM

inner join Employee E ON E.EmployeeGUID = CM.FKGUID

INNER JOIN ConfigChoice CC ON CC.ConfigChoiceID = CM.ContactTypeConfigChoiceID 

 

SELECT DISTINCT '['+ContactType+']'ContactType  INTO #ContactTypes

FROM #ContactTypeAndValue

--select * from #ContactTypes

DECLARE @forpiviot varchar(255)

SELECT  top 1

@forpiviot = STUFF(

(SELECT ','+ContactType  FROM #ContactTypes FOR XML PATH ('')), 1, 1, ''

) 

FROM #ContactTypes 

--select @forpiviot

DECLARE @sql NVARCHAR(MAX)

  SET @Sql = N'

    

SELECT E.FirstName,E.LastName, PivotTable.* FROM (

  SELECT *

  FROM #ContactTypeAndValue 

) Finalres

PIVOT (

  MAX([ContactMethodValue])

  FOR [ContactType]

  

      IN (

        '+@forpiviot+'

      )

) AS PivotTable 

INNER JOIN Employee E ON E.EmployeeGUID = PivotTable.EmployeeGUID

  ';

 

  EXEC(@Sql)

  

drop table #ContactTypeAndValue

drop table #ContactTypes