SQL Server partial pivot

Question

I had an earlier question about this problem but I realized I left a few things out, so I'll just post it again with the additional information rather than confuse everyone by editing the old post...

I have some data that looks something like this:

AssessmentID    QuestionText AnswerText    Ordinal
--              --           --            --
1               One          Alpha         10
1               Two          Beta          20
1               Three        Gamma         30
2               One          Aleph         10
2               Two          Bet           20
2               Three        Gimel         30

I want to pivot the data, but only partially - each assessment ID should get its own row:

AssessmentID Question1 Answer1 Question2 Answer2 Question3 Answer3
--           --        --      --        --      --        --
1            One       Alpha   Two       Beta    Three     Gamma
2            One       Aleph   Two       Bet     Three     Gimel

I tried pivoting the data, but I'm having trouble with the syntax:

SELECT *
    FROM  
    (select QuestionText, AnswerText, AssessmentID, (select count(*) from @temp b where b.Ordinal <= a.Ordinal and a.AssessmentID = b.AssessmentID) as Idx  from @temp a) AS SourceTable  
    PIVOT  
    (  
        max(AssessmentID)
        FOR Idx IN ([1],[2],[3])
    ) AS PivotTable;  

So I want the numbers in the column headers to be the index of the ordinal within the assessment, e.g. since 20 is the second ordinal for assessment 1, it gets treated as Question2/Answer2. Then the actual questions and answers get pulled from that particular record. But what I'm getting here is QuestionText and AnswerText for each individual answer, and then three columns 1, 2, and 3 which are almost always null, and the AssessmentID is left off entirely:

QuestionText  AnswerText  1    2    3
--            --          --   --   --
One           Alpha       NULL NULL NULL
Two           Beta        NULL NULL NULL
Three         Gamma       NULL NULL NULL
One           Aleph       NULL NULL NULL
Two           Bet         NULL NULL NULL
Three         Gimel       NULL NULL NULL

How can I change the pivot statement to give me the data I need?

Thanks! :)

Answer 1

Use conditional aggregation:

select AssessmentID,
       max(case when seqnum = 1 then QuestionText end) as questiontext_1,
       max(case when seqnum = 1 then AnswerText end) as answertext_1,
       max(case when seqnum = 2 then QuestionText end) as questiontext_2,
       max(case when seqnum = 2 then AnswerText end) as answertext_2,
       max(case when seqnum = 3 then QuestionText end) as questiontext_3,
       max(case when seqnum = 3 then AnswerText end) as answertext_3
from (select t.*,
             row_number() over (partition by AssessmentID order by ordinal) as seqnum
      from @temp t
     ) t
group by AssessmentID

Answer 2

declare @a table
(
AssessmentID  int,
QuestionText varchar(10),
AnswerText varchar(10),
Ordinal int
);

insert into @a(AssessmentID, QuestionText, AnswerText, Ordinal)
values
(1, 'One', 'Alpha', 10),(1, 'Two', 'Beta', 20),(1, 'Three', 'Gamma', 30), (1, 'Four', 'Delta', 40), (1, 'Five', 'Epsilon', 50),
(2, 'One', 'Aleph', 10),(2, 'Two', 'Bet', 20),(2, 'Three', 'Gimel', 30);


select pvt.*
from
(
    select unpv.AssessmentID, unpv.QAText, 
    concat(replace(unpv.QAType, 'Text', ''), /*replace?,  case when QuestionText then Question else Answer ?*/ 
           row_number() over(partition by unpv.AssessmentID, unpv.QAType order by unpv.Ordinal /*tie breaker*/)
           ) as QAOrdinal
    from @a
    unpivot
    (
    QAText for QAType in (QuestionText, AnswerText)
    ) as unpv
) as u
pivot
(
max(QAText) for QAOrdinal in (Question1, Answer1, Question2, Answer2, Question3, Answer3, Question4, Answer4, Question5, Answer5)
) as pvt;