I am creating a dynamic pivot query using SQL Server 2008 but got stuck in STUFF.
Example:
Declare @col as nvarchar(max)
Declare @tablename as nvarchar(max)
SET @col = 'STUFF((SELECT '','' + QUOTENAME(cola)
from ' + @tablename + '
group by cola
order by cola
FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)'')
,1,1,'''')'
execute(@col)
print @col;
Error
Incorrect syntax near the keyword 'order'.
Declare @col as nvarchar(max)
Declare @tablename as nvarchar(max) = N'Table1'
set @col = N'SELECT
STUFF((
SELECT
'','' + QUOTENAME(cola)
FROM ' + @tablename + N'
GROUP BY
cola
ORDER BY
cola
FOR xml PATH (''''), TYPE
)
.value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''');'
execute(@col)
;
not exec @col
see: this SQLFiddle demo
Meem, I have modified your query and it should look like this.
Declare @col as nvarchar(max)
Declare @tablename as nvarchar(max)
set @col = 'REF_REFM_CODE'
set @tablename = 'tblKeywords'
SET @col = 'Select STUFF((SELECT ' + ''','' + ' + @col + '
from ' + @tablename + '
group by ' + @col +
' order by ' + @col +
' FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)'')
,1,1,'''')'
execute(@col)
print @col;
See this Demo in SQL Fiddle.
Declare @col as nvarchar(max) Declare @tablename as nvarchar(max)
SET @col = 'STUFF((SELECT '','' + QUOTENAME(cola) from '''+ @tablename +''' group by cola order by cola FOR XML PATH(''''), TYPE ).value(''.'', ''NVARCHAR(MAX)'') ,1,1,'''')' execute(@col)
print @col;
Note: i tried this and u check it once.
Incorrect syntax near ' + @tablename + '.