Generate dynamic columns using Pivot function in SQL Server

jmprateek
 
on Mar 29, 2021 12:37 AM
609 Views

Hello,

I'm Prateek, trying to create an application that takes user inputs from the controls that are being generated dynamically such as a TextBox or a DropDownList.

These controls are generated from the database values itself i.e. I'm storing all the values that are to be created in a table named 'usertasks'.

Now, everything is working fine and controls are dynamically generated, the user inputs are stored in another table named 'taskEntries'. The problem I'm facing is with the values being fetched:

https://i.stack.imgur.com/mr8fj.png

Now I want the data to be fetched:

https://i.stack.imgur.com/q5Vl0.png

Kindly let me know how I do it in asp.net c# sql

This is what I tried so far: 

if OBJECT_ID('tempdb..##TBL_TEMP') IS NOT NULL
DROP TABLE ##TBL_TEMP
declare @SQLQUERY as nvarchar(max)
declare @PivotColumns as nvarchar(max)

select @PivotColumns = coalesce(@PivotColumns + ', ', '') + QUOTENAME(question) from dbo.taskEntries
--select @PivotColumns
set @SQLQUERY = N'[question], [userInput],' + @PivotColumns + ' 
into ##TBL_TEMP 
from dbo.taskEntries
PIVOT (Max([userInput])

FOR [question] IN ('+ @PivotColumns +')) AS Q'
--select @SQLQUERY as 'SQLQUERY'

EXEC sp_executesql @SQLQuery

but the above query throws an error.

Download FREE API for Word, Excel and PDF in ASP.Net: Download