SQL Pivot for multiple values

Question

Here is the data that i have

   Product_ID      Question      Answer
      1             When          Monday
      1             Where         Home
      1             Where         Work
      1              How          Car
      2             When          saturday
      2             Where         Home
      2             Where         Church
      2              How           Bus
      2              How           Walk

I would like the data to presented as below

  Product_ID        When         Where   How
      1             Monday       Home    Car
      1             Monday       Work    Car
      2             Saturday     Home    Bus
      2             Saturday     Church  Bus
      2             Saturday     Home    Walk
      2             Saturday     Church  Walk

But I was only able to pivot the data and was able to get in the below style

  Product_ID        When         Where
      1             Monday       Work
      2             Saturday     Home

using the below query

 select * from 
(
select product_ID,question,answer from table1
) src
pivot (
max(answer)
for question in ([when],[where])
)piv

This isn't really a pivot. What happens if there's more than one 'When'? Do you get 4 results then? What happens if there's other questions? Do you basically need a cartesian product of all the questions? — ZLK
– ZLK, Commented Nov 14, 2016 at 1:40
I do not think it is a cartesian product, i have added more rows to show when there are more than on 'When'. If there is another question, then that would be another column — RData
– RData, Commented Nov 14, 2016 at 2:14
I meant more questions within a product_id, or is it always just 1 'when' and x 'where's? — ZLK
– ZLK, Commented Nov 14, 2016 at 2:50
basically there are a set of 2-3 repitative questions that are asked for each product_id and there is 1 'When' and multiple 'Where's' — RData
– RData, Commented Nov 14, 2016 at 2:57

ZLK · Accepted Answer · 2016-11-15 03:45:58Z

2

As mentioned in the comments, this doesn't really require a PIVOT. One way of achieving your desired results (assuming there's only two questions: 'when' and 'where') is to use a self-join with subqueries:

SELECT T1.Product_ID, T1.[When], T2.[Where]
FROM (
    SELECT Product_ID, Answer [When]
    FROM table1
    WHERE Question = 'When'
    ) T1
JOIN (
    SELECT Product_ID, Answer [Where]
    FROM table1
    WHERE Question = 'Where') T2 ON T2.Product_ID = T1.Product_ID;

Alternatively, use an APPLY to achieve the same thing:

SELECT *
FROM (
    SELECT Product_ID, Answer [When]
    FROM table1
    WHERE Question = 'When'
    ) T1
CROSS APPLY (
    SELECT Answer [Where]
    FROM table1
    WHERE Product_ID = T1.Product_ID
    AND Question = 'Where') T2;

EDIT: Here is one way to achieve the desired result with dynamic SQL:

/* -- Sample data
CREATE TABLE table1 (Product_ID INT NOT NULL, Question VARCHAR(20) NOT NULL, Answer VARCHAR(20) NOT NULL);
INSERT table1 
    VALUES (1, 'When', 'Monday')
         , (1, 'Where', 'Home')
         , (1, 'Where', 'Work')
         , (1, 'How', 'Car')
         , (2, 'When', 'Saturday')
         , (2, 'Where', 'Home')
         , (2, 'Where', 'Church')
         , (2, 'How', 'Bus')
         , (2, 'How', 'Walk')
         , (3, 'Where', 'Park'); -- No other questions for this Product_ID
*/

DECLARE @SQL VARCHAR(MAX) = '', @cols VARCHAR(MAX) = '', @joins VARCHAR(MAX) = '', @coalesce VARCHAR(MAX) = 'COALESCE';
SELECT @coalesce += CASE RN WHEN 1 THEN '(' ELSE ',' END + 'T' + CAST(RN AS VARCHAR(4)) + '.Product_ID'
     , @cols += ', T' + CAST(RN AS VARCHAR(4)) + '.' + QUOTENAME(Question)
     , @joins += 
       CASE RN 
        WHEN 1
        THEN '
FROM (SELECT Product_ID, Answer ' + QUOTENAME(Question) + ' FROM table1 WHERE Question = ''' + Question + ''') T1 '
        ELSE '
FULL JOIN (SELECT Product_ID, Answer ' + QUOTENAME(Question) + ' FROM table1 WHERE Question = ''' + Question + ''') T' + CAST(RN AS VARCHAR(4)) + ' ON T' + CAST(RN AS VARCHAR(4)) + '.Product_ID = T1.Product_ID'
       END
FROM (SELECT Question, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN FROM (SELECT DISTINCT Question FROM table1) T) T
ORDER BY RN;
SELECT @coalesce += ') Product_ID';

SELECT @SQL = 'SELECT ' + @coalesce + @cols + @joins;
PRINT @SQL;
EXEC(@SQL);

edited Nov 15, 2016 at 3:45

answered Nov 14, 2016 at 3:25

ZLK

2,8941 gold badge13 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RData Over a year ago

Self-Join works and CROSS APPLY does not because he second query does not give me results that have 'Where' column as NULL. Also, what is there are more than 2 questions ? would it be able to make it dynamic ?

ZLK Over a year ago

Yes it's possible, but what sort of result set would you be looking for? e.g. If there was a "why" or something, what would the result set look like?

RData Over a year ago

I have added another question and i would like to know if there is a way to include the question dynamically without mentioning explicitly

ZLK Over a year ago

I've added a way you could do this dynamically.

Collectives™ on Stack Overflow

SQL Pivot for multiple values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related