Sql Server 2008 - PIVOT without Aggregation Function

Question

I know you've got multiple topics touching on this. But, I havent found one that addressed my needs. I need to (on demand) pivot select deep table data to a wide output table. The gotcha in this is that I cannot use an aggregate with Pivot because it eats responses that are needed in the output. I have worked up to a solution, but I don't think it's the best because it will require umpteen left joins to work. I've included all attempts and notes as follows:

-- Sql Server 2008 db.
-- Deep table structure (not subject to modification) contains name/value pairs with a userId as
-- foreign key.  In many cases there can be MORE THAN ONE itemValue given by the user for the
-- itemName such as if asked their race, can answer White + Hispanic, etc.  Each response is stored
-- as a seperate record - this cannot currently be changed.

-- Goal: pivot deep data to wide while also compressing result 
-- set down. Account for all items per userId, and duplicating
-- column values (rather than show nulls) as applicable

-- Sample table to store some data of both single and multiple responses
DECLARE @testTable AS TABLE(userId int, itemName varchar(50), itemValue varchar(255))

INSERT INTO @testTable
SELECT 1, 'q01', '1-q01 Answer'
UNION SELECT 1, 'q02', '1-q02 Answer'
UNION SELECT 1, 'q03', '1-q03 Answer 1'
UNION SELECT 1, 'q03', '1-q03 Answer 2'
UNION SELECT 1, 'q03', '1-q03 Answer 3'
UNION SELECT 1, 'q04', '1-q04 Answer'
UNION SELECT 1, 'q05', '1-q05 Answer'
UNION SELECT 2, 'q01', '2-q01 Answer'
UNION SELECT 2, 'q02', '2-q02 Answer'
UNION SELECT 2, 'q03', '2-q03 Answer 1'
UNION SELECT 2, 'q03', '2-q03 Answer 2'
UNION SELECT 2, 'q04', '2-q04 Answer'
UNION SELECT 2, 'q05', '2-q05 Answer'

SELECT 'Raw Data'
SELECT * FROM @TestTable

SELECT 'Using Pivot - shows aggregate result of itemValue per itemName - eats others'
; WITH Data AS (
    SELECT
        [userId]
        , [itemName]
        , [itemValue]
    FROM 
        @testTable
)
SELECT
    [userId]
    , [q02]
    , [q03]
    , [q05]
FROM
    Data
PIVOT
(
    MIN(itemValue)  -- Aggregate function eats needed values.
    FOR itemName in ([q02], [q03], [q05])
) AS PivotTable


SELECT 'Aggregate with Grouping - Causes Null Values'
SELECT
    DISTINCT userId 
    ,[q02] = Max(CASE WHEN itemName = 'q02' THEN itemValue END)
    ,[q03] = Max(CASE WHEN itemName = 'q03' THEN itemValue END)
    ,[q05] = Max(CASE WHEN itemName = 'q05' THEN itemValue END)
FROM
    @testTable
WHERE
    itemName in ('q02', 'q03', 'q05')   -- Makes it a hair quicker
GROUP BY
    userId  -- If by userId only, it only gives 1 row PERIOD = BAD!!
    , [itemName]
    , [itemValue]


SELECT 'Multiple Left Joins - works properly but bad if pivoting 175 columns or so'
; WITH Data AS (
    SELECT
        userId 
        ,[itemName]
        ,[itemValue]
    FROM
        @testTable
    WHERE
        itemName in ('q02', 'q03', 'q05')   -- Makes it a hair quicker
)
SELECT
    DISTINCT s1.userId
    ,[q02] = s2.[itemValue]
    ,[q03] = s3.[itemValue]
    ,[q05] = s5.[itemValue]
FROM
    Data s1
    LEFT JOIN Data s2 
        ON s2.userId = s1.userId 
            AND s2.[itemName] = 'q02'
    LEFT JOIN Data s3 
        ON s3.userId = s1.userId 
            AND s3.[itemName] = 'q03'
    LEFT JOIN Data s5 
        ON s5.userId = s1.userId 
            AND s5.[itemName] = 'q05'

So the bottom query is the only one (so far) that does what I need it to do, but the LEFT JOIN's WILL get out of hand and cause performance issues when I use actual item names to pivot. Any recommendations are appreciated.

Your LEFT JOIN solution works only because in your sample data, every user has got only one question with multiple answers, other questions have single answers. Add another answer to, say, question `q05` and try your LEFT JOIN method again – you will get duplicates. So, my question is, what should the output be when there are more than one question with multiple answers per user? — Andriy M, Oct 22 '11 at 00:34
That's because in this scenario, Only q03 would allow for multiple responses, where the rest would only allow for 1 response. I guess I could make a ton more sample data, but thought this would be enough to convey my issue. — Danny Grogan, Oct 25 '11 at 14:43
@Andriy. For clarification. The output should essentially be a cross-tab style showing each response given for each item on a different row (like the left join attempt did). It should duplicate single response items for multiple response items to the extent that if there are multiple - multiple response items, each multiple response item, gets it's own group of rows per each response of the other multiple item groups. Does this help, or did I muddy the water more? — Danny Grogan, Oct 25 '11 at 15:33
I think I've got it. In other words, there would be a series of mini-Cartesian products of responses, i.e. every user gets their own Cartesian product of their responses. Or in yet other words, for every user, there would be every possible combination of their responses (each from a different question). Is that close? — Andriy M, Oct 25 '11 at 16:28
I believe we're on the same page. It will effectively result in the report viewer seeing that when user x answered question y in a certain way, the user also answered question z in way one, two, etc. For example, if question y is 'do you smoke' and question z is what is your race, they can anwer yes to smoking, and we'll see the user is white, hispanic and pacific islander, allowing for us to compute how many people both smoke, and are of hispanic descent. — Danny Grogan, Oct 25 '11 at 16:43

score 3 · Answer 1 · answered Oct 25 '11 at 17:43

I think you'll have to stick with joins, because joins are exactly the way of producing results like the one you are after. The purpose of a join is to combine row sets together (on a condition or without any), and your target output is nothing else than a combination of subsets of rows.

However, if the majority of questions always have single responses, you could substantially reduce the number of necessary joins. The idea is to join only multiple-response groups as separate row sets. As for the single-response items, they are joined only as part of the entire dataset of target items.

An example should better illustrate what I might poorly describe verbally. Assuming there are two potentially multiple-response groups in the source data, 'q03' and 'q06' (actually, here's the source table:

DECLARE @testTable AS TABLE(
  userId int,
  itemName varchar(50),
  itemValue varchar(255)
);

INSERT INTO @testTable
SELECT 1, 'q01', '1-q01 Answer'
UNION SELECT 1, 'q02', '1-q02 Answer'
UNION SELECT 1, 'q03', '1-q03 Answer 1'
UNION SELECT 1, 'q03', '1-q03 Answer 2'
UNION SELECT 1, 'q03', '1-q03 Answer 3'
UNION SELECT 1, 'q04', '1-q04 Answer'
UNION SELECT 1, 'q05', '1-q05 Answer'
UNION SELECT 1, 'q06', '1-q06 Answer 1'
UNION SELECT 1, 'q06', '1-q06 Answer 2'
UNION SELECT 1, 'q06', '1-q06 Answer 3'
UNION SELECT 2, 'q01', '2-q01 Answer'
UNION SELECT 2, 'q02', '2-q02 Answer'
UNION SELECT 2, 'q03', '2-q03 Answer 1'
UNION SELECT 2, 'q03', '2-q03 Answer 2'
UNION SELECT 2, 'q04', '2-q04 Answer'
UNION SELECT 2, 'q05', '2-q05 Answer'
UNION SELECT 2, 'q06', '2-q06 Answer 1'
UNION SELECT 2, 'q06', '2-q06 Answer 2'
;

which is same as the table in the original post, but with added 'q06' items), the resulting script could be like this:

WITH ranked AS (
  SELECT
    *,
    rn = ROW_NUMBER() OVER (PARTITION BY userId, itemName ORDER BY itemValue)
  FROM @testTable
),
multiplied AS (
  SELECT
    r.userId,
    r.itemName,
    r.itemValue,
    rn03 = r03.rn,
    rn06 = r06.rn
  FROM ranked r03
    INNER JOIN ranked r06 ON r03.userId = r06.userId AND r06.itemName = 'q06'
    INNER JOIN ranked r ON r03.userId = r.userId AND (
      r.itemName = 'q03' AND r.rn = r03.rn OR
      r.itemName = 'q06' AND r.rn = r06.rn OR
      r.itemName NOT IN ('q03', 'q06')
    )
  WHERE r03.itemName = 'q03'
    AND r.itemName IN ('q02', 'q03', 'q05', 'q06')
)
SELECT userId, rn03, rn06, q02, q03, q05, q06
FROM multiplied
PIVOT (
  MIN(itemValue)  
  FOR itemName in (q02, q03, q05, q06)
) AS PivotTable

Wow! It will take me a bit of time to fully grasp the concepts, but I believe you nailed it. I hadn't concidered seperating the single/multi response items, since I was generating the query dynamically - it was just easier to do them all the same. I appreciate your (and everyone else's) assistance. — Danny Grogan, Oct 25 '11 at 18:34
I modified your solution a bit, and actually think - based on your recommendations, I got this thing beat. Additionally, something I hadn't noted, was the deep table doesn't store items with nulls - it just doesn't include them, so if q03 hadn't been answered at all, your solution would have shown no items for that userid showed. Take a look at my answer and see if you agree. — Danny Grogan, Oct 25 '11 at 19:59

score 3 · Accepted Answer · answered Oct 25 '11 at 19:59

; WITH SRData AS (
    SELECT  -- Only query single response items in this block
        [userId]
        , [q01]
        , [q02]
        , [q04]
        , [q05]
    FROM
        @testTable
    PIVOT
    (
        MIN(itemValue) 
        FOR itemName in ([q01], [q02], [q04], [q05])
    ) AS PivotTable
)
SELECT
    sr.[userId]
    , sr.[q01]
    , sr.[q02]  
    , [q03] = mr03.[itemValue]
    , sr.[q04]      
    , sr.[q05]      
    , [q06] = mr06.[itemValue]
FROM
    SRData sr
    LEFT JOIN @testTable mr03 ON mr03.userId = sr.userId AND mr03.itemName = 'q03'  -- Muli Response for q03
    LEFT JOIN @testTable mr06 ON mr06.userId = sr.userId AND mr06.itemName = 'q06'  -- Muli Response for q06

This is brilliantly simple. I definitely overdid mine. – Andriy M Oct 27 '11 at 09:27 — Andriy M, Oct 27 '11 at 09:27

score 2 · Answer 3 · answered Oct 21 '11 at 22:05

2

Not clear what the desired results should look like exactly but one possibility

; WITH Data AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY [userId], [itemName] 
                               ORDER BY [itemValue]) AS RN
        ,  [userId]
        , [itemName]
        , [itemValue]
    FROM 
        @testTable
)
SELECT
    [userId]
    , [q02]
    , [q03]
    , [q05]
FROM
    Data
PIVOT
(
    MIN(itemValue)  
    FOR itemName in ([q02], [q03], [q05])
) AS PivotTable

Returns

userId      q02                            q03                            q05
----------- ------------------------------ ------------------------------ ------------------------------
1           1-q02 Answer                   1-q03 Answer 1                 1-q05 Answer
1           NULL                           1-q03 Answer 2                 NULL
1           NULL                           1-q03 Answer 3                 NULL
2           2-q02 Answer                   2-q03 Answer 1                 2-q05 Answer
2           NULL                           2-q03 Answer 2                 NULL

answered Oct 21 '11 at 22:05

Martin Smith

438,706
87
741
845

This is closest to what the OP wants, I think. NULLs are the only thing not accounted for, but then, the OP is yet to answer my question about multiple questions with multiple answers per user. Their 'best' solution doesn't handle such cases correctly while yours does. – Andriy M Oct 22 '11 at 00:43
Unfortunately not.. The nulls is what kills it... The result must appear like a cross tab, where folks that answered all three responses in q03, would have their other answers dupped in each duplicated row. Does that make sense? – Danny Grogan Oct 25 '11 at 14:46
@DannyGrogan - Not really. So if there were 3 responses for `Q03`, 4 for `q02` and 5 for `q05` you would want `3*4*5 =60` rows? – Martin Smith Oct 25 '11 at 14:50
@MartinSmith. Sounds nuts right, but essentially you're on track? If you add a multi-response item, like q06 like UNION SELECT 1, 'q06', '1-q06 Answer 1' UNION SELECT 1, 'q06', '1-q06 Answer 2' UNION SELECT 1, 'q06', '1-q06 Answer 3' UNION SELECT 2, 'q06', '2-q06 Answer 1' UNION SELECT 2, 'q06', '2-q06 Answer 3' Then, adjust the bottom query to actually return q06 items, you should get 13 rows that duplicate each response accordingly. – Danny Grogan Oct 25 '11 at 15:45

Sql Server 2008 - PIVOT without Aggregation Function

3 Answers3