[Solved] SQL Server dynamically Pivot Query Error: Invalid column name

Hi Muddassar

I have error in generatin Store Procedure query. The error is

Msg 207, Level 16, State 1, Line 1 Invalid column name 'BALANCE'.

Eventhough the data table having Balance column.

pls advice me. Thank you

Maideen

Below is my code 

		DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
		DECLARE @ColumnName AS NVARCHAR(MAX)

		SELECT @ColumnName= ISNULL(@ColumnName + ',','')
			   + QUOTENAME(YEARNO)
		FROM (SELECT DISTINCT YEARNO FROM tbl_Ledger_Statement) AS vYEARNO

		SET @DynamicPivotQuery =
		  N'SELECT BALANCE,GLCODE,GLNAME,GLGROUP ' + @ColumnName + '
			FROM tbl_Ledger_Statement 
			PIVOT(SUM(BALANCE) 
			FOR YEARNO IN (' + @ColumnName + ')) AS PVTTable'
		EXEC sp_executesql @DynamicPivotQuery