Cacher is the code snippet organizer for pro developers

-------------------------------------------------------------------------------------
http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/
-------------------------------------------------------------------------------------
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(SubSectionName)
FROM (
	SELECT DISTINCT hmSubSection.NAME AS SubSectionName
	FROM dbo.AuditSubsectionDetail asd
	INNER JOIN dbo.HierarchyMaster hmSubSection ON hmSubSection.Id = asd.SubsectionId AND hmSubSection.IsActive = 1
	WHERE asd.IsActive = 1
	) AS SubSections

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = N'
SELECT AuditId,' + @ColumnName + '
FROM (
	SELECT asd.AuditId
		,hmSubSection.NAME
		,asd.ObtainedScoreOnWeightage
	FROM dbo.AuditSubsectionDetail asd
	INNER JOIN dbo.HierarchyMaster hmSubSection ON hmSubSection.Id = asd.SubsectionId
	) tbl
PIVOT(MAX(ObtainedScoreOnWeightage) FOR NAME IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
--EXEC USP_Rpt_GetEngineerSkillAtpScoreForAccount 1 ,2  ,2    
ALTER PROCEDURE USP_Rpt_GetEngineerSkillAtpScoreForAccount @centerId INT
	,@AccountId INT
	,@AtpId INT
AS
--DECLARE @centerId INT = 1      
-- ,@AccountId INT = 2      
BEGIN
	--CREATE TABLE #tblSrc (
	--	EngineerName VARCHAR(255)
	--	,SkillName VARCHAR(255)
	--	,Rating INT
	--	)

	--INSERT INTO #tblSrc (
	--	EngineerName
	--	,SkillName
	--	,Rating
	--	)
	SELECT u.UserName
		,s.SkillName
		,trs.Rating AS Rating
	FROM dbo.Account a
	INNER JOIN dbo.AccountXUserCenterRole axcr ON axcr.IsActive = 1
		AND axcr.AccountId = a.Id
	INNER JOIN dbo.UserXCenterXRole ucr ON ucr.UserXCenterXRoleId = axcr.UserCenterRoleId
		AND ucr.IsActive = 1
	INNER JOIN dbo.UserXCenter uc ON ucr.UserXCenterId = uc.UserXCenterId
		AND uc.IsActive = 1
		AND uc.CenterId = @centerId
	INNER JOIN dbo.[User] u ON u.UserId = uc.UserId
		AND uc.IsActive = 1
	INNER JOIN dbo.AccountXCenterPguSkill axps ON axps.AccountId = a.Id
		AND axps.IsActive = 1
	INNER JOIN dbo.CenterXPGUXSkill cps ON cps.CenterXPGUXSkillId = axps.CenterPguSkillId
		AND cps.IsActive = 1
	INNER JOIN dbo.Skill s ON s.SkillId = cps.SkillId
		AND s.IsActive = 1
	LEFT JOIN dbo.UserCenterXCenterPguSkill ucxps ON ucxps.UserXCenterId = ucr.UserXCenterId
		AND ucxps.CenterXPGUXSkillId = axps.CenterPguSkillId
		AND ucxps.IsActive = 1
	LEFT JOIN dbo.Assessment a2 ON ucxps.Id = a2.UserCenterXCenterPguSkillId
		AND a2.IsActive = 1
	LEFT JOIN dbo.TCIAssessmentResultSummary trs ON a2.AssessmentId = trs.AssessmentId
		AND a.IsActive = 1
		AND trs.ActivityId = @AtpId
	WHERE a.IsActive = 1
		AND a.Id = @AccountId

	--SELECT * FROM #tblSrc ts  
	DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
	DECLARE @ColumnName AS NVARCHAR(MAX)

	------Get distinct values of the PIVOT Column   
	--SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(SkillName)
	--FROM (
	--	SELECT DISTINCT SkillName
	--	FROM #tblSrc
	--	) AS Skills

	----SELECT @ColumnName  
	------Prepare the PIVOT query using the dynamic   
	--SET @DynamicPivotQuery = N'SELECT EngineerName, ' + @ColumnName + '  
 --   FROM #tblSrc  
 --   PIVOT(SUM(Rating)   
 --         FOR SkillName IN (' + @ColumnName + ')) AS PVTTable'

	------Execute the Dynamic Pivot Query  
	--EXEC sp_executesql @DynamicPivotQuery

	--DROP TABLE #tblSrc
END