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:

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

CodePudding user response：

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

Demo on db<>fiddle here

CodePudding user response：

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;