sql - Dynamic Pivot table error -

i having trouble getting following work. have table, 'tbl_view', exists in database has column names of table held in rows (users able choose specific columns wish display , list of choices kept here).

i want create view bring each users specific column choices target table 'areg' , 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 nvarchar(max) declare @columnname nvarchar(max)  --get distinct values of pivot column  select @columnname= isnull(@columnname + ',','') + quotename(fldname) (select distinct fldname qry_adet (viewid=14)) fname   --prepare pivot query using dynamic  set @dynamicpivotquery = n'select ' + @columnname + ' #areg   pivot ' + @columnname + ' pvttable' --execute dynamic pivot query exec sp_executesql @dynamicpivotquery 

i keep getting error:

msg 102, level 15, state 1, line 1

incorrect syntax near 'text_1'.

i have hard coded view id query example use parameter in final query referenced asp.net datasource. error points different column name if change viewid clause.

please help.

thanks

to debug dynamic sql query, print statement before executing it.

you see error @dynamicpivotquery - should selecting tbl_view instead of #areg right ? - pivot statement wrong. should like

from sometable pivot (      sum( somecol)      anothercol in ( [value1], [value2] , [value3] ) ) p