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...
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
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.