How do I dynamically pivot row values into columns without aggregare?

Question

Lets say I have the following table representing results from a user survey system.

SurveyID    ResponseID  QuestionID  Answer
 --------   ----------  ----------  ------
1           1           1           'Answer 1'
1           1           2           'Answer 2'
1           1           3           'Answer 3'
1           2           1           'red'
1           2           2           'blue'
1           2           3           'green'

What I want is a pivoted output such that shown below.

SurveyID    ResponseID  Q1          Q2          Q3
--------    ----------  --          --          --
1           1           'Answer 1'  'Answer 2'  'Answer 3'
1           2           'red'       'blue'      'green'

I know how to achieve this if there were always only the same three questions but this database hosts multiple surveys which could have any number of unique QuestionIDs so I need the Q1, Q2, Q3 columns to be dynamic depending upon the number and IDs of that survey's questions.

I thought this would be a fairly standard problem but I cannot find anything that fully satisfies this issue. Any solution must work with SQL Server 2005.

Hope that makes sense. Thanks.

Take a look at the second answer on this link http://stackoverflow.com/questions/17700957/dynamic-pivot-for-multiple-columns/17701731?noredirect=1#comment25796267_17701731...does something that your looking for — Dev N00B, Jul 17 '13 at 15:01

score 0 · Answer 1 · answered Jul 17 '13 at 15:24

1) Pivots need an aggregate. You may know in advance that you are only interested in one row, but SQL doesn't know that. If you are only dealing with one row per group, just use MIN() as your aggregate.

2) Dynamic pivot is not a standard problem for SQL. That's a task for the presentation layer, not the data layer. You will have to use dynamic SQL, which still won't be able to handle an arbitrary number of columns and will open up injection attacks if you aren't careful.

If you still want to do it this way:

CREATE TABLE #t (Surveyid int, Responseid int, Questionid int, Answer varchar(max))
INSERT #t VALUES (1,1,1,'Answer1'),(1,1,2,'Answer2'),(1,1,3,'Answer3'),(1,2,1,'red'),(1,2,2,'blue'),(1,2,3,'green')

DECLARE @qids nvarchar(4000)

SELECT @qids = COALESCE(@qids+',','') + qid
FROM (SELECT DISTINCT QUOTENAME(Questionid) qid FROM #t) t

EXEC ('SELECT [SurveyID],[ResponseID],'+@qids+' FROM #t PIVOT(MIN(Answer) FOR Questionid IN('+@qids+')) p')

score 0 · Answer 2 · answered Jul 18 '13 at 14:13

OK, finally discovered how to do this so thought I would share.

DECLARE @SurveyID SMALLINT;
DECLARE @SQL as VARCHAR(MAX);
DECLARE @Columns AS VARCHAR(MAX);
DECLARE @ColumnHeadings AS Varchar(MAX);

SET @SurveyID = 1;

SELECT
    @Columns = COALESCE(@Columns + ', ','') + '[' + QuestionID + ']'
    ,@ColumnHeadings = COALESCE(@ColumnHeadings + ', ','') + '[' + QuestionID + '] AS [Q' + QuestionNumber + ']'
FROM
(
    SELECT DISTINCT
        CAST(a.QuestionID AS VARCHAR) AS QuestionID
        ,CASE WHEN q.QuestionNumber IS NULL THEN '' ELSE q.QuestionNumber END AS QuestionNumber
    FROM dbo.Answers AS a
    JOIN dbo.Questions AS q
        ON a.QuestionID = q.ID
    JOIN dbo.SurveyResponses AS r
        ON a.ResponseID = r.ID
    WHERE r.SurveyID = @SurveyID
)

SET @SQL = '
WITH PivotData AS
(
    SELECT
        a.QuestionID
        ,a.ResponseID
        ,a.Answer
    FROM dbo.Answers AS a
    JOIN dbo.SurveyResponses AS r
        ON a.ResponseID = r.ID
)
SELECT 
    ResponseID
,' + @ColumnHeadings + '
FROM PivotData
PIVOT
(
    MAX(Answer)
    FOR QuestionID
    IN (' + @Columns + ')
) AS PivotResult
ORDER BY ResponseID' ASC

EXEC (@SQL);

How do I dynamically pivot row values into columns without aggregare?

2 Answers2