Search code examples
t-sqlsql-server-2016window-functions

TSQL Window Function

I have a table with three columns:

Survey   QuestionName   SurveyQuestion     SurveyAnswer
1859     Satisfied?     1                  Yes
1859     Comments?      2                  Happy with outcome, thanks
1859     Phone Number?  3                  Not Answered
2000     Satisfied?     1                  No
2000     Comments?      2                  Rep was a d1ck. 
2000     Phone Number?  3                  5166569999

What I want to end up with is a table that looks like this (Note: SurveyQuestion #3 is not needed)

Survey   SurveyAnswer1    SurveyAnswer2     
1859     Yes              Happy with outcome, thanks
2000     No               Rep was a d1ck.

I just can't think of how to do this... I've tried several ways including LEAD/LAG, but can't get it to work... I think I just need someone to point me in the right direction 😞

Solution

  • That's four columns ;)

    You can use the old aggregate-as-crosstab chestnut:

    select   Survey,
         SurveyAnswer1 = max(iif(SurveyQuestion = 1, SurveyAnswer, null)),
         SurveyAnswer2 = max(iif(SurveyQuestion = 2, SurveyAnswer, null))
from     YourTable
where    SurveyQuestion != 3
group by Survey