SQL Dynamic Pivot Query

Question

I need help with a pivot table if possible. Not sure it can be done. Below is my example. Questions

Answers

User Answers

Answer Pivot

Thanks For any help.

I have attached a screenshot of db results with the pivot working . The code below pivot as it should and that works fine. If you can look at the column "Subject has exp..." I would like the column repeated three times, The heading is the question, But I also would like each possible answer to shown with the question as well.

Thank Again!

Thansk Again.

Here Is a Sample of the Code:

DECLARE @columns VARCHAR(MAX)

SELECT  @columns = COALESCE(@columns + ','+QUOTENAME([QuestionText]),QUOTENAME([QuestionText]))

FROM         SchemaWema.vts_vwSurvey INNER JOIN

                      Question ON SchemaWema.vts_vwSurvey.SurveyID = SchemaWema.vts_vwQuestion.SurveyID
WHERE SchemaWema.vts_vwSurvey.DecisionSetId = 598 AND SchemaWema.vts_vwSurvey.FormType = 1

Set @columns =  REPLACE(@columns, '[]', '[No Name Given]')

DECLARE @sql NVARCHAR(MAX)=N' SELECT  * FROM
(

        SELECT     Project.PROJECTNAME, Project.ID, 
                        Sites.PROJECTSTUDYSITENUMBER,  Sites.PROJECTSTUDYSITENAME,
                        Study.PROJECTSTUDYNAME, Study.PROJECTSTUDYNUMBER,
                        Survey.SurveyID,     Question.QuestionID,  Cases.SITEID, Cases.EVENTDATE, 
                              Cases.SUBJNO, Cases.EventType, Cases.TriggerEvent, 
                              Cases.DecisionSetID, Cases.PanelID, Cases.RECORDSTATUS, 
                              Project.PROJECTSPONSOR,  VoterAnswers.AnswerID, VoterAnswers.VoterID,
                              VoterAnswers.AnswerText AS FreeTypedAnswers, Voter.VoteDate, Users.SHOWNNAME, Panel.PANELNAME,  Sites.COUNTRY,
                              Cases.VERSIONID, cases.CSID, cases.CASENUMBER,Cases.CASEDATEUPDATED AS LASTUPDATED,
                              Cases.DECISIONSETNAME,
                              Question.QuestionText,
                        CASE 
                            WHEN
                                CAST(VoterAnswers.AnswerText AS VARCHAR(MAX)) IS NULL OR CAST(VoterAnswers.AnswerText AS VARCHAR(MAX)) = ''''
                            THEN
                                Answer.AnswerText
                        ELSE
                            CAST(Answer.AnswerText AS VARCHAR(MAX)) 

                    END AS AnswerText
          FROM
        (
                SELECT     a.ID, a.STUDYID, a.SITEID, a.CSID, a.VERSIONID, a.CASENUMBER, a.CASEINITIALS, a.EVENTDATE, a.CASEDETAILSFILE, a.CASEDETAILSFILEVERSION, 
                                      a.CASESTATUS, a.RECORDSTATUS, a.CASEDATEUPDATED, a.PROJECTDATEUPDATED, a.PanelID, a.DecisionSetID, a.SUBJNO, a.EventType, a.TriggerEvent, 
                                      a.Priority, a.id1, a.id2, a.dateid, a.dateiddt1, a.dateiddt2, a.extratext1, a.extratext2, SchemaWema.PROJECTPANELDECISIONSETVIEW.DECISIONSETNAME
                FROM         SchemaWema.PROJECTCASE AS a LEFT OUTER JOIN
                                      SchemaWema.PROJECTPANELDECISIONSETVIEW ON a.DecisionSetID = SchemaWema.PROJECTPANELDECISIONSETVIEW.DECISIONSETID
                WHERE     (a.CASEDATEUPDATED =
                                          (SELECT     MAX(CASEDATEUPDATED) AS Expr1
                                            FROM          SchemaWema.PROJECTCASE AS b
                                            WHERE      (a.ID = PROJECTID) AND (a.CSID = CASEID) AND (a.VERSIONID = VERSIONID) )) AND (a.RECORDSTATUS <> ''D'')
        )Cases
        LEFT OUTER JOIN
        (
                SELECT     PROJECTID, PROJECTNAME,  PROJECTSPONSOR
                FROM         SchemaWema.PROJECT AS a
                WHERE     (PROJECTDATEUPDATED =
                                  (SELECT     MAX(PROJECTDATEUPDATED) AS Expr1
                                    FROM          SchemaWema.PROJECT AS b
                                    WHERE      (a.ID = PROJECTID))) AND (RECORDSTATUS <> ''D'')

        ) Project on
        Project.ID = Cases.ID
        LEFT OUTER JOIN
        (
                SELECT  DISTINCT  a.ID, a.STUDYID, a.SITEID, a.PROJECTSTUDYSITENAME, a.PROJECTSTUDYSITENUMBER, SchemaWema.PROJECTSTUDYVIEW.PROJECTSTUDYNAME, a.COUNTRY
                FROM         SchemaWema.PROJECTSTUDYSITE AS a INNER JOIN
                                      SchemaWema.PROJECTVIEW ON a.ID = SchemaWema.PROJECTVIEW.ID INNER JOIN
                                      SchemaWema.PROJECTSTUDYVIEW ON a.ID = SchemaWema.PROJECTSTUDYVIEW.ID AND 
                                      a.STUDYID = SchemaWema.PROJECTSTUDYVIEW.STUDYID
                WHERE     (a.PROJECTSTUDYSITEDATEUPDATED =
                                          (SELECT    MAX(PROJECTSTUDYSITEDATEUPDATED) AS Expr1
                                            FROM          SchemaWema.PROJECTSTUDYSITE AS b
                                            WHERE      (a.ID = PROJECTID) AND (a.STUDYID = STUDYID) AND (a.SITEID = SITEID))) AND (a.RECORDSTATUS <> ''D'')

        )Sites on
        Cases.SITEID = Sites.SITEID 
        LEFT OUTER JOIN
        (
                SELECT  DISTINCT   PROJECTID, STUDYID, PROJECTSTUDYNAME, PROJECTSTUDYNUMBER, PROJECTSTUDYDESCRIPTION
                FROM         SchemaWema.PROJECTSTUDY AS a
                WHERE     (PROJECTSTUDYDATEUPDATED =
                                          (SELECT     MAX(PROJECTSTUDYDATEUPDATED) AS Expr1
                                            FROM          SchemaWema.PROJECTSTUDY AS b
                                            WHERE      (a.ID = PROJECTID) AND (ISNULL(a.STUDYID, ''0'') = ISNULL(STUDYID, ''0'')))) AND (RECORDSTATUS <> ''D'')
        )Study on
        Study.STUDYID = Cases.STUDYID

         inner join
         (
                SELECT     VoterID, UID, SurveyID, ContextUserName, VoteDate, StartDate, IPSource, Validated, ResumeUID, ResumeAtPageNumber, ProgressSaveDate, 
                            ResumeQuestionNumber, ResumeHighestPageNumber, LanguageCode, SurveyStatus, VoteAcceptRejectDate, CaseID, VersionID, PdfFileName, voterSurveyStatus, 
                            dateupdated, recordstatus, modifiedby, changereason
                FROM         SchemaWema.vts_tbVoter AS a
                WHERE     (dateupdated =
                                          (SELECT     MAX(dateupdated) AS Expr1
                                            FROM          SchemaWema.vts_tbVoter AS b
                                            WHERE      (a.VoterID = VoterID) AND (a.SurveyID = SurveyID) AND (a.CSID = CaseID) AND (a.VersionID = VersionID)))

         ) Voter on

         Voter.CSID = Cases.CSID
        AND Voter.VersionID = Cases.VersionID
        Inner Join
          (
                    SELECT  DISTINCT   SurveyID, DecisionSetId, FormType 

                        FROM         SchemaWema.vts_tbSurvey AS a
                        WHERE     (dateupdated =
                                  (SELECT     MAX(dateupdated) AS Expr1
                                    FROM          SchemaWema.vts_tbSurvey AS b
                                    WHERE      (a.SurveyID = SurveyID))) AND (recordstatus <> ''D'')
        ) Survey on
         Survey.SurveyID = Voter.SurveyID 
        inner Join
        (
                SELECT  DISTINCT
                                      VoterID, AnswerID, SectionNumber, CAST(AnswerText AS varchar(MAX)) AnswerText, SurveyID, CaseID, versionID
                FROM         SchemaWema.vts_tbVoterAnswers AS a
                WHERE     (dateupdated =
                                  (SELECT  MAX(dateupdated) AS Expr1
                                    FROM          SchemaWema.vts_tbVoterAnswers AS b
                                    WHERE      (a.VoterID = VoterID) AND (a.AnswerID = AnswerID) AND (a.SectionNumber = SectionNumber) AND (a.SurveyID = SurveyID) AND (a.CSID = CaseID) AND 
                                                           (a.versionID = versionID))) AND (recordstatus <> ''D'')
        )VoterAnswers on
        VoterAnswers.SurveyID = Survey.SurveyID
        AND VoterAnswers.CSID = Voter.CSID
        AND Voter.VoterID = VoterAnswers.VoterID
        AND VoterAnswers.versionID = Voter.versionID
        AND VoterAnswers.SurveyID = Voter.SurveyID
        FULL OUTER JOIN
        (

                select  DISTINCT a.USERID, a.USERNAME, a.FIRSTNAME, a.LASTNAME, a.SHOWNNAME
                from SchemaWema.users as a
                WHERE (a.userdateupdated =
                     (SELECT     MAX(b.userdateupdated) AS Expr1
                     FROM          SchemaWema.users AS b
                     WHERE   (a.userID = b.userID)  )) 
                 AND (a.RECORDSTATUS <> ''D'')
        ) Users on
        VoterAnswers.VoterID = Users.USERID
        AND Voter.VoterID = Users.USERID
        inner join
        (
                SELECT   DISTINCT  AnswerID, QuestionID,  AnswerText FROM         SchemaWema.vts_tbAnswer AS a
                            WHERE     (dateupdated =
                                (SELECT     MAX(dateupdated) AS Expr1
                                FROM          SchemaWema.vts_tbAnswer AS b
                                WHERE      (a.AnswerID = AnswerID))) AND (recordstatus <> ''D'')
        ) Answer  on
          Answer.AnswerID = VoterAnswers.AnswerID 
          inner Join
          (
                    SELECT   DISTINCT  QuestionID,SurveyID,  QuestionText
                        FROM         SchemaWema.vts_tbQuestion AS a
                        WHERE     (dateupdated =
                                  (SELECT     MAX(dateupdated) AS Expr1
                                    FROM          SchemaWema.vts_tbQuestion AS b
                                    WHERE      (a.QuestionID = QuestionID))) AND (recordstatus <> ''D'')
        ) Question on
          Answer.QuestionID = Question.QuestionID
          AND Question.SurveyID = Survey.SurveyID
          AND Question.SurveyID = VoterAnswers.SurveyID
        inner join
        (
                SELECT DIStinct
                      [PANELID]  ,[PANELNAME]
                  FROM [AdjudicateV3].[Adjudicate].[PROJECTPANELVIEW]
        )Panel on
        Panel.PANELID = Cases.PanelID

        WHERE  CASEs.ID = ' + @ID + ' AND Survey.DecisionSetId = '+ DeeSetId +' AND Survey.FormType = '+ @Forms +'

) AS SourceTable
PIVOT
(
        MAX(AnswerText)
        FOR QuestionText IN ('+@columns+')
) AS PivotTable'

If you want help writing the query you need to provide some details for us. This would be a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Oct 16 '15 at 18:36
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Juan Carlos Oropeza, Oct 16 '15 at 18:53
Use a dynamic pivot to generate the columns, and a CASE statement to either put an "X" or not in each column for each row. — Tab Alleman, Oct 16 '15 at 18:53

score 1 · Accepted Answer · answered Oct 16 '15 at 19:45

It can be done using a dynamic pivot.

First thing you have to do is build the cols you're going to use for the pivot..

declare @cols varchar(max)
select @cols = Coalesce(@cols + ', ', '') + '[' + q.Question + '(' + a.Question + ')]'
from Questions q
join Answers a on q.ID = a.QuestionID

this will concatenate all of the column headers you want into one string like

[What is your favorite Car Color(Blue)], [What is your favorite Car Color(Red)]

putting the column names inside brackets is important here...

The next step is building your pivot query

declare @sql varchar(max)
set @sql = 'Select [User], ' + @cols + ' from (
            select ua.[User], ''X'' as Chosen, 
                q.Question + ''('' + a.Question + '')'' Answer
            from UserAnswers ua
            join Answers a On ua.AnswerId = a.ID and ua.QuestionID = a.QuestionID
            join Questions q on a.QuestionID = q.ID 
            ) t
            pivot (
               max(Chosen)
               for Answer IN (' + @cols + ')
            ) p'
exec(@sql)

this will create a subquery with three columns User, Chose, Answer.. user is user name, chosen is just an X for every record, and the Answer, which is what is used to pivot. Answer looks like the column names above without the brackets..

next it pivots the subquery.. and selects the Name and dynamic columns..

SQL Fiddle Example

SQL Dynamic Pivot Query

1 Answers1