Return 1 row per unique id instead of multiple rows

Question

I have a sql query which returns a list of students which are enrolled in a conference, and their preferences for each session. When pulling the data from my database, each user session selection is showing in its own row as shown below:

**userid      question              answer**
1        S1 choose: a1, b1, c1      a1
1        S2 choose: a2, b2, c2      b2
1        S3 choose: a3, b3, c3      b3
2        S1 choose: a1, b1, c1      b1
2        S2 choose: a2, b2, c2      c2
2        S3 choose: a3, b3, c3      a3
3        S1 choose: a1, b1, c1      a1
3        S2 choose: a2, b2, c2      b2
3        S3 choose: a3, b3, c3      b3

I would like to make each session a colum, so that each userid with the questions and answers is shown in one row. Like so:

user1 question1 answer1 question2 answer2 question3 answer3
user2 question1 answer1 question2 answer2 question3 answer3
user3 question1 answer1 question2 answer2 question3 answer3

I'm limited to my SQL query knowledge so I would greatly appreciate your help....how can I reach the above results? Thanks in advance

Which `RDBMS` are you using? And do you know the number of questions or is it dynamic? — sgeddes, May 28 '14 at 22:43
I'm using Microsoft SQL Server Management Studio 2008. The number of questions varies per conference. — user3356789, May 28 '14 at 22:43
Also, it is often easier to do this with presentation code such as .net, coldfusion, etc. Is that available to you? — Dan Bracuk, May 28 '14 at 22:44
You should perform the output formating with a regular programing language, iterating over the results. You are trying to combine 2 columns of 3 rows into 6 columns with 1 row... that's no logical conversion for a Query language. — dognose, May 28 '14 at 22:44
@DanBracuk I don't have presentation code available for now. — user3356789, May 28 '14 at 22:46
@user3356789 -- What you're trying to do is `pivot` your results and since you don't know the number of columns, you'll need to use `dynamic sql` to do this. Here is an example: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — sgeddes, May 28 '14 at 22:48
@sgeddes what if I knew the number of columns, how can I do this? — user3356789, May 28 '14 at 22:59
If you know the number of columns is also a `PIVOT` but you can skip the dynamic part — Serpiton, May 28 '14 at 23:55

sgeddes · Accepted Answer · 2014-05-29T00:35:22.913

In response to your comment, assuming you know the number of potential questions, one option is to use max with case:

select userid,
       max(case when questionid = 1 then question end) question1,
       max(case when questionid = 1 then answer end) answer1,
       max(case when questionid = 2 then question end) question2,
       max(case when questionid = 2 then answer end) answer2,
       ...
from yourtable
group by userid

This assumes you have a questionid available. If not, you could use the question field or create a ROW_NUMBER and it would work the same way:

select userid,
       max(case when rn = 1 then question end) question1,
       max(case when rn = 1 then answer end) answer1,
       max(case when rn = 2 then question end) question2,
       max(case when rn = 2 then answer end) answer2,
       ...
from (
    select *, row_number() over (partition by userid order by question) rn
    from yourtable
) t
group by userid

Edit, if you need a dynamic solution for this, since you are trying to pivot multiple columns, you first need to unpivot your results. One option for doing this is to use CROSS APPLY. Then you can PIVOT the results back:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME('Question:' + question) +',' + QUOTENAME('Answer:' + question) 
                    from  yourtable
                    group by question
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'select userid, ' + @cols + '
from
(
  select userid,
    col+question new_col,
    value
  from yourtable
  cross apply
  (
    VALUES
        (question, ''Question:''),
        (answer, ''Answer:'')
   ) x (value, col)
) src
pivot
(
  max(value)
  for new_col in (' + @cols + ')
) piv '

execute(@query)

SQL Fiddle Demo

Thakns @sgeddes. For now I'm creating them non-dynamically, but soon would have to make it dynamic SQL. — user3356789, May 29 '14 at 18:34

score 0 · Answer 2 · edited May 28 '14 at 23:58

Use dynamic SQL. Select a distinct list of sessions into a temp table that you've created with an identity column.

DECLARE @SQL varchar(1000), @Count int, @Counter int
CREATE TABLE #report (userid int)
CREATE TABLE #questions (MyIdx int IDENTITY(1,1), question varchar(50))

INSERT INTO #report (UserID) SELECT DISTINCT userid from mytable
INSERT INTO #questions (question) SELECT DISTINCT question FROM mytable ORDER BY question

SELECT @Count = COUNT(*) FROM #questions, @Counter = 0

WHILE @Counter < @Count
BEGIN
    SET @Counter = @Counter + 1

    SET @SQL = 'ALTER TABLE #report ADD Q' + CONVERT(varchar, @Counter) + ' varchar(50)'

    EXEC (@SQL)

    SET @SQL = 'ALTER TABLE #report ADD A' + CONVERT(varchar, @Counter) + ' varchar(50)'

    EXEC (@SQL)

    SET @SQL = 'UPDATE #report SET Q' + CONVERT(varchar, @Counter) + ' = b.question FROM #report a INNER JOIN mytable b ON a.userid = b.userid INNER JOIN #questions c ON b.question = c.question WHERE c.MyIdx = ' + CONVERT(varchar, @Counter)

    EXEC (@SQL)

    SET @SQL = 'UPDATE #report SET A' + CONVERT(varchar, @Counter) + ' = b.answer FROM #report a INNER JOIN mytable b ON a.userid = b.userid AND a.question = b.Q' + CONVERT(varchar, @Counter)

    EXEC (@SQL)
END

SELECT * FROM #report ORDER BY userid

DROP TABLE #report

DROP TABLE #questions

That is, if you have an unknown number of sessions. Otherwise, go with the other answer.

score 0 · Answer 3 · answered May 29 '14 at 00:19

If the example in the question is similar to your data a possibility is to split and union

SELECT userid
     , q1 = max(q1), a1 = max(a1)
     , q2 = max(q2), a2 = max(a2)
     , q3 = max(q3), a3 = max(a3)
FROM   (SELECT userid
             , q1 = question, a1 = answer
             , q2 = NULL, a2 = NULL
             , q3 = NULL, a3 = NULL
        FROM   table1
        WHERE  left(question, 2) = 'S1'
        UNION ALL
        SELECT userid
             , q1 = NULL, a1 = NULL
             , q2 = question, a2 = answer
             , q3 = NULL, a3 = NULL
        FROM   table1
        WHERE  left(question, 2) = 'S2'
        UNION ALL
        SELECT userid
             , q1 = NULL, a1 = NULL
             , q2 = NULL, a2 = NULL
             , q3 = question, a3 = answer
        FROM   table1
        WHERE  left(question, 2) = 'S3') d
GROUP BY userid

SQLFiddle demo

Each question code is used to split the data, while the other attribute a placed in the wanted columns. Every data split have userid so it can be used as anchor for grouping the other values to reduce the mapping to one line per userid.

Return 1 row per unique id instead of multiple rows

3 Answers3