sql query help - multiple responses into one

Question

I hope I can describe this well enough for you to get what I need. I have a table with questionnaire responses, columns are:

cust_id
question_number
response 
datetimestamp

So my table looks like this:

cust_id	question_no	response
1234	1	YES
1234	2	NO
1234	3	TAKEN
1234	4	NOT GIVEN
4567	1	NO
4567	3	NOT TAKEN
8888	2	YES
8888	4	GIVEN

basically i need a query which will give me each cust_id and each question and response - like this:

cust_id	question_1	response_1	question_2	response_2	question_3	response_3	question_4	response_4
1234	1	YES	2	NO	3	taken	4	not given
4567	1	NO	3	NOT TAKEN
8888	2	YES	4	GIVEN

Please review the [question guide](https://stackoverflow.com/help/how-to-ask), help us help you and edit your question to include a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — Stu, Dec 11 '21 at 11:00
You need to use the PIVOT function. See [here](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server/15745076#15745076) for an example — NickW, Dec 11 '21 at 11:34

score 0 · Answer 1 · answered Dec 11 '21 at 12:04

If you union the questions to the responses, then you can pivot them.

SELECT *
FROM
(
    SELECT 
      cust_id
    , [col] = CONCAT('response_', 
                     row_number() over (partition by cust_id 
                                        order by question_no))
    , concat(response, null) as [value]
    FROM customer_question_responses
    UNION ALL
    SELECT 
      cust_id
    , [col] = CONCAT('question_', 
                     row_number() over (partition by cust_id 
                                        order by question_no))
    , concat(question_no, null) as [value]
    FROM customer_question_responses
) Src
PIVOT
(
  MAX([value]) 
  FOR [Col] IN (
   [question_1], [response_1], 
   [question_2], [response_2], 
   [question_3], [response_3], 
   [question_4], [response_4]
  ) 
) Pvt
ORDER BY cust_id

cust_id	question_1	response_1	question_2	response_2	question_3	response_3	question_4	response_4
1234	1	YES	2	NO	3	TAKEN	4	NOT GIVEN
4567	1	NO	3	NOT TAKEN	null	null	null	null
8888	2	YES	4	GIVEN	null	null	null	null

Demo on db<>fiddle here

score 0 · Answer 2 · answered Dec 11 '21 at 20:48

It's easier to pivot using conditional aggregation than using PIVOT if you have multiple columns to pivot.

In this case, just add a row-number and conditionally aggregate on that:

SELECT
  t.cust_id,
  question_1 = MAX(CASE WHEN t.rn = 1 THEN t.question_no END),
  response_1 = MAX(CASE WHEN t.rn = 1 THEN t.response END),
  question_2 = MAX(CASE WHEN t.rn = 2 THEN t.question_no END),
  response_2 = MAX(CASE WHEN t.rn = 2 THEN t.response END),
  question_3 = MAX(CASE WHEN t.rn = 3 THEN t.question_no END),
  response_3 = MAX(CASE WHEN t.rn = 3 THEN t.response END),
  question_4 = MAX(CASE WHEN t.rn = 4 THEN t.question_no END),
  response_4 = MAX(CASE WHEN t.rn = 4 THEN t.response END)
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY question_no)
    FROM YourTable t
) t;

sql query help - multiple responses into one

2 Answers2