Dynamic Pivot table error

I am currently having trouble getting the following to work. I have a table, 'tbl_View', that exists in my database that has the column names of another table held in its rows (users are able to choose specific columns that they wish to display and their list of their choices is kept here).

I then want to create a view that will bring only each users specific column choices from the target table 'AREG' and would like to do so dynamically if possible.

Table Samples - AREG Column names:

ID | Text_1 | Text_2 | Text_3 | Text_4 etc...

tbl_View Data:

ViewID | lblValue | fldName | showYN | sOrder

10 | EANPR | YesNo8 | True | 80

10 | INSP_DATE| Date_1 | True | 81

10 | KG_DATE | Date_2 | True | 82

11 | Life | Num_13 | True | 35

11 | Area | Dimension_1 | True | 69

11 | Length | Dimension_2 | True | 70

11 | EANPR | YesNo8 | True | 80

12 | TRAVEL | Text_1 | True | 1

12 | SPILLLVE | Text_2 | True | 2

12 | SLOPE_PCT| Text_3 | True | 3

14 |Project_Name | Text_1 | True | 1

14 | Project Description | Text_2 | 2

code is:

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

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(fldName)
FROM (SELECT DISTINCT fldName FROM qry_ADet WHERE (Viewid=14)) AS fname 

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = N'SELECT ' + @ColumnName + ' FROM #AREG   PIVOT ' + @ColumnName + ' AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

I keep getting the error:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'Text_1'.

I have hard coded the view id into the query for this example but will use a parameter in my final query as it will be referenced by an asp.net datasource. But the error points to a different column name if you change the Viewid clause.

Please help.

Thanks


ANSWERS:


to debug a dynamic sql query, do a PRINT statement before executing it.

You will probably see that the error is from @DynamicPivotQuery - you should be selecting from tbl_View instead of #AREG right ? - your pivot statement is wrong. it should be something like

FROM sometable
PIVOT
(
     sum( somecol)
     for anothercol in ( [value1], [value2] , [value3] )
) p

too long for comment, I think the issue may stem from the column concatenation and it needs something like this:

SET @ColumnName = STUFF((SELECT distinct ',' + QUOTENAME(fldName) 
            FROM qry_ADet
             WHERE (Viewid=14)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


 MORE:


 ? SQL Pivot Query with Dynamic Columns
 ? SQL Pivot Query with Dynamic Columns
 ? SQL Pivot Query with Dynamic Columns
 ? T-SQL Pivot? Possibility of creating table columns from row values
 ? SQL Server : Querying Multiple Rows, Returning In One
 ? Need to pivot or crosstab a table but not in the conventional way. please
 ? How to write a query which will result in the following format?
 ? How do I PIVOT in SQL Server with dynamic columns, values, and datatypes?
 ? Multi Column Dynamic Pivot Table
 ? Dynamic PIVOT with Numbered Column Names