pivot dinamico

[jepequi]

Hola.
De antemano les agradezco sus respuestas.
Tengo el siguiente código para intentar hacer un pívot dinámico:
DECLARE @TableYears AS TABLE(excont nvarchar(50) NOT NULL)
DECLARE @Year nvarchar(50), @YearsPVT NVARCHAR(500)
INSERT INTO @TableYears SELECT DISTINCT excont FROM tempjai
SET @Year = (SELECT MIN(excont) FROM @TableYears)
SET @YearsPVT=N''
WHILE @Year IS NOT NULL
BEGIN
SET @YearsPVT = @YearsPVT + N',['+ CONVERT(NVARCHAR(10),@Year) + N']'
SET @Year = (SELECT MIN(excont) FROM @TableYears WHERE excont>@Year)
END
SET @YearsPVT = SUBSTRING(@YearsPVT,2,LEN(@YearsPVT))
--PRINT @YearsPVT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT *
FROM (
SELECT esnrso,excont, exvari FROM [109_11_11]
) pvt
PIVOT (sum(exvari) FOR excont IN (' + @YearsPVT + ')) AS PVJPQ
ORDER by esnrso'
EXECUTE sp_executesql @SQL

Pero cuando lo ejecuto me sale este error q no he podido corregir:
Unclosed quotation mark after the character string '0000)) AS PVJPQ
ORDER by esnrso'.

Alguien me puede ayudar gracias.

[matanga]

Es difícil saber el valor final de @sql, pero puedes agregar el código print @sql antes del sp_executesql para ver los errores de sintaxis en la consulta generada.

Saludos

[jepequi]

Bueno ps ya solucione el error... la verdad no supe era asi q por si alguien lo necesita dejo el codigo del q me funciono...

DECLARE @TableYears AS TABLE([Year] nvarchar(50) NOT NULL)
DECLARE @Year nvarchar(50), @YearsPVT NVARCHAR(max)
INSERT INTO @TableYears SELECT DISTINCT EXVARI AS [Year] FROM tempjai
SET @Year = (SELECT MIN([Year]) FROM @TableYears)
SET @YearsPVT=N''
WHILE @Year IS NOT NULL
BEGIN
SET @YearsPVT = @YearsPVT + N',['+ CONVERT(NVARCHAR(50),@Year) + N']'
SET @Year = (SELECT MIN([Year]) FROM @TableYears WHERE [Year]>@Year)
END
SET @YearsPVT = SUBSTRING(@YearsPVT,2,LEN(@YearsPVT))
--PRINT @YearsPVT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT *
FROM (
SELECT ESNRSO, EXVARI [Year],excont FROM tempjai
) pvt
PIVOT (max(excont) FOR [Year] IN (' + @YearsPVT + ')) AS PVJPQ
ORDER by esnrso'
EXECUTE sp_executesql @SQL