Can Sql Server 2005 Pivot table have nText passed into it?
- by manemawanna
Right bit of a simple question can I input nText into a pivot table? (SQL Server 2005)
What I have is a table which records the answers to a questionnaire consisting of the following elements for example:
UserID QuestionNumber Answer
Mic 1 Yes
Mic 2 No
Mic 3 Yes
Ste 1 Yes
Ste 2 No
Ste 3 Yes
Bob 1 Yes
Bob 2 No
Bob 3 Yes
With the answers being held in nText. Anyway what id like a Pivot table to do is:
UserID 1 2 3
Mic Yes No Yes
Ste Yes No Yes
Bob Yes No Yes
I have some test code, that creates a pivot table but at the moment it just shows the number of answers in each column (code can be found below). So I just want to know is it possible to add nText to a pivot table? As when I've tried it brings up errors and someone stated on another site that it wasn't possible, so I would like to check if this is the case or not.
Just for further reference I don't have the opportunity to change the database as it's linked to other systems that I haven't created or have access too.
Heres the SQL code I have at present below:
DECLARE @query NVARCHAR(4000)
DECLARE @count INT
DECLARE @concatcolumns NVARCHAR(4000)
SET @count = 1
SET @concatcolumns = ''
WHILE (@count <=52)
BEGIN
IF @COUNT > 1 AND @COUNT <=52
SET @concatcolumns = (@concatcolumns + ' + ')
SET @concatcolumns = (@concatcolumns + 'CAST ([' + CAST(@count AS NVARCHAR) + '] AS NVARCHAR)')
SET @count = (@count+1)
END
DECLARE @columns NVARCHAR(4000)
SET @count = 1
SET @columns = ''
WHILE (@count <=52)
BEGIN
IF @COUNT > 1 AND @COUNT <=52
SET @columns = (@columns + ',')
SET @columns = (@columns + '[' + CAST(@count AS NVARCHAR) + '] ')
SET @count = (@count+1)
END
SET @query = '
SELECT UserID,
' + @concatcolumns + '
FROM(
SELECT
UserID,
QuestionNumber AS qNum
from QuestionnaireAnswers
where QuestionnaireID = 7
) AS t
PIVOT
(
COUNT (qNum)
FOR qNum IN (' + @columns + ')
) AS PivotTable'
select @query
exec(@query)
