Piviot pro max

 select Distinct e.EmployeeGUID,e.EmployeeID,evc.ChoiceCode as extravalueChoice,ev.Value,utc.ChoiceCode userTypeChoice,mu.UserName userTypeUser,dtc.ChoiceCode as dateTypeChoice,mud.UserName as dateTypeUser into #bigG  from Employee e -- 

left join EmployeeCertification ec on ec.EmployeeGUID=e.EmployeeGUID

left join EmployeeSkill es on es.EmployeeGUID=e.EmployeeGUID

left join ExtraValue ev on ev.FKGUID=e.EmployeeGUID

left join UserType ut on ut.FKGUID=e.EmployeeGUID

left join DateType dt on dt.FKGUID=e.EmployeeGUID

left join ConfigCertificationType cc on cc.ConfigCertificationTypeGUID=ec.ConfigCertificationTypeGUID

left join ConfigSkillCode cs on cs.ConfigSkillCodeID=es.ConfigSkillCodeId

left join ConfigChoice evc on evc.ConfigChoiceID=ev.LabelConfigChoiceID

left join ConfigChoice utc on ut.UserTypeConfigChoiceID=utc.ConfigChoiceID

left join ConfigChoice dtc on dtc.ConfigChoiceID=dt.DateTypeConfigChoiceID

left join MasterUser mu on mu.UserGUID=ut.UserGUID

left join MasterUser mud on mu.UserGUID=mud.UserGUID

order by 1

select distinct EmployeeGUID as certiG ,

STUFF((SELECT distinct ','+  y.CertificationName from ConfigCertificationType as y

inner join EmployeeCertification ec on ec.ConfigCertificationTypeGUID=y.ConfigCertificationTypeGUID

Where ec.EmployeeGUID=g.EmployeeGUID

For XML PATH ('')),1,1,'') [certification],

STUFF((SELECT distinct ','+  y.SkillCode from ConfigSkillCode as y

inner join EmployeeSkill es on es.ConfigSkillCodeID=y.ConfigSkillCodeID

Where es.EmployeeGUID=g.EmployeeGUID

For XML PATH ('')),1,1,'') [skills]

into #skillandcerti from #bigG g

select Distinct EmployeeGUID extrag ,extravalueChoice,value into #Extras from #bigG

select Distinct EmployeeGUID userg ,userTypeChoice,userTypeUser into #userty from #bigG

SELECT DISTINCT CONCAT('[', userTypeChoice,']')userTypeChoice  INTO #usertype

FROM #bigG

if exists (select 1 from #usertype where userTypeChoice='[]')

begin 

  update #usertype set userTypeChoice='[noColumn1]' where userTypeChoice='[]'

end

--select * from #usertype

DECLARE @forpiviotuser varchar(max)

SELECT  top 1

@forpiviotuser = STUFF(

(SELECT Distinct ','+userTypeChoice  FROM #usertype FOR XML PATH ('')), 1, 1, ''

) 

FROM #usertype 

SELECT DISTINCT CONCAT('[', extravalueChoice,']')extravalueChoice  INTO #ExtraVal

FROM #bigG

if exists (select 1 from #ExtraVal where extravalueChoice='[]')

begin 

  update #ExtraVal set extravalueChoice='[noColumn2]' where extravalueChoice='[]'

end

--select * from #ExtraVal

DECLARE @forpiviotExtra varchar(max)

SELECT  top 1

@forpiviotExtra = STUFF(

(SELECT Distinct ','+extravalueChoice FROM #ExtraVal FOR XML PATH ('')), 1, 1, ''

) 

FROM #ExtraVal 

DECLARE @sql NVARCHAR(MAX)

  SET @Sql = N'

    

  SELECT E.EmployeeGUID,E.FirstName+E.LastName as EmployeeName,PivotTable.* into #piv1 FROM (

  SELECT *

  FROM #Extras

) Finalres

PIVOT (

  MAX([Value])

  FOR [extravalueChoice]

  

  IN (

'+@forpiviotExtra+'

  )) AS PivotTable 

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

SELECT PivotTable.* into #piv2 FROM (

  SELECT *

  FROM #userty

) Finalres

PIVOT (

  MAX([userTypeUser])

  FOR [userTypeChoice]

  

  IN (

'+@forpiviotuser+'

  )) AS PivotTable 

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

select * into #mainpiv from  #piv1 p1 inner join #piv2 p2

on p1.extrag=p2.userg

inner join #skillandcerti sc on sc.certiG=p1.extrag

alter Table #mainpiv drop column userg

alter Table #mainpiv drop column extrag

alter Table #mainpiv drop column certiG

select * from #mainpiv

';

 

  exec(@Sql)

 

    drop table #bigG

drop table #usertype

drop table #ExtraVal

drop table #Extras

Drop table #skillandcerti

drop table #userty