How do i join the rows into dynamic columns in thi

Question

This question already has an answer here:

• Pivot Dynamic Columns, no Aggregation 1 answer

I'm sorry to post this, what i feel, almost duplicate. But i've tried the solutions i've found but haven't gotten it to work in my solution :(

This is how the SQL looks before i FUBARed it. This returns the data in a pretty good format. But i get duplicate rows with the same data except that the questions and answers are changed. I'd like that the Question would be the column name and the answer it's value.

SELECT c.*, sa.Question, sa.Answer
    FROM Customers as c, Surveys s, SurveyAnswers sa 
    WHERE c.OrderID IN(SELECT id FROM @orders)
        AND s.CustomerID = c.id
        AND sa.SurveyID = s.ID

My SQL is weak and i got to get this done asap :( The alternative is to do the more heavy lifting in the .net app but i'd be nice to get the data directly Best regards, Mikael

Answer 1

You want to use Bluefeet's answer here to accomplish a dynamic column pivot. (I've omitted your @orders filter for brevity):

DECLARE 
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(sa.Question) 
            FROM SurveyAnswers sa
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT CustomerID, Name, ' + @cols + ' from 
            (
                select 
                    c.ID as CustomerId, 
                    c.Name,
                    sa.Question, 
                    sa.Answer
                FROM Customers as c
                     INNER JOIN Surveys s ON s.CustomerID = c.id
                     INNER JOIN SurveyAnswers sa ON sa.SurveyID = s.ID
           ) x
            pivot 
            (
                min(Answer)
                for Question in (' + @cols + ')
            ) p '
 execute(@query);

SqlFiddle here