flip rows to column in SQL Server

Below is a table that i use in the example;

AttendeeName GroupName QuestionName AnswerName QuestionId Value Result Order SurveyId AnswerId
Ahmet Ege Kisisel Bilgiler Askerlik Yaptiniz mi? (Q1) Evet (A1) 1 1 5 1 11 4
Ahmet Ege Kisisel Bilgiler Evli misiniz? (Q2) Hayır (A2) 2 1 6 3 11 5
Ahmet Ege Kisisel Bilgiler Is ariyor musunuz? (Q3) Evet (A3) 3 1 1 2 11 7

and the result expectation is;

AttendeeName Askerlik Yaptiniz mi? (Q1) Evli misiniz? (Q2) Is ariyor musunuz? (Q3)
Ahmet Ege Evet (A1) Evet (A2) Evet (A3)

here is the Query you have to be considered;


SET @SurveyId = '11' ;
DECLARE @QuestionList nvarchar(max);

SELECT @QuestionList =
            STUFF(
                              (
                                 SELECT ', ' + quotename(QuestionName)
                                 FROM custom_survey_reports
                                 WHERE oti_surveyId = @SurveyId
                                 GROUP BY QuestionName
                                 ORDER BY QuestionName
                                 FOR XML PATH('')
                                ) , 1, 2, ''
            );
DECLARE @qry nvarchar(max);
SET @qry = '
            SELECT AttendeeName, ' + @QuestionList + '
            FROM
                        (
                                    SELECT AttendeeName, QuestionName, AnswerName
                                    FROM custom_survey_reports
                                    WHERE surveyId = ' + '''' + @SurveyId + '''' + '
                         ) up ' + '
            PIVOT (
                          MAX(AnswerName) FOR QuestionName IN
                                    (' + @QuestionList + ')
                        ) pvt;';
 exec sp_executesql @qry;


Text statement of
            "PIVOT (MAX(AnswerName) FOR QuestionName IN (' + @QuestionList + ')) pvt"
is :
            do rows to the columns based on QuestionName IN QuestionLists
            and group them  by AnswerName and take MAX of the records.


hopes help,

Comments

Popular posts from this blog

Assembly Microsoft.Dynamics.Service.Plugins.dll can not be loaded. Dynamics CRM 365 Engine version 9 - CRM User creation error

Exception caught instantiating TERADATA report server extension SQL Reporting Services

Could not load file or assembly 'System.ServiceModel, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. The system cannot find the file specified at Configuration class initiation in CrmServiceHelper.cv