SQL Select Distinct row values as Column headers maintain individual row values

Question

I have a table that looks like this:

    QuestionNum  AnswerChoice
    1            a
    1            a
    2            b
    2            b
    2            a
    3            c
    3            d
    3            c
    4            a
    4            b

I would like to select the distinct values from the QuestionNum column as column headers and still list each answer choice underneath, so it should look like this:

    1    2    3    4
    a    b    c    a
    a    b    d    b
         a    c

I started looking at Pivot tables, but the QuestionNum is going to be unknown. Also, I couldnt figure out a way to select multiple rows from the original.

how do you know which ones should go on which rows? is there some questionnaire ID that ties them together? If there isn't you could create one. That is what you would pivot on — Leslie, Oct 06 '15 at 14:20
Yes, I left out that column. Technically these are all associated with an examInstanceID. Each exam has X number of questions, but I don't think I can pivot on the examInstanceID because the questions per exam may be different since they are drawn semi-randomly from the database. examInstanceId 1 may have questions 1, 2, 3, 4 but examInstanceId 2 may have question 2, 3, 4, 5. — jpsnow72, Oct 06 '15 at 14:30
you can dynamically generate the list of questions that will become the columns http://stackoverflow.com/questions/14797691/dynamic-pivot-columns-in-sql-server — Leslie, Oct 06 '15 at 14:31
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — sstan, Oct 06 '15 at 15:09

score 0 · Answer 1 · answered Oct 06 '15 at 15:01

You can do this with conditional aggregation. The challenge is that you need a key, and row_number() provides the key:

select max(case when QuestionNum = 1 then AnswerChoice end) as q_1,
       max(case when QuestionNum = 2 then AnswerChoice end) as q_2,
       max(case when QuestionNum = 3 then AnswerChoice end) as q_3,
       max(case when QuestionNum = 4 then AnswerChoice end) as q_4       
from (select t.*,
             row_number() over (partition by QuestionNum order by examInstanceID) as seqnum
      from table t
     ) t
group by seqnum;

SQL Select Distinct row values as Column headers maintain individual row values

1 Answers1