Return multiple row's column values as columns of linked item

Question

I have a (simplified) table that is structured like so:

Table: ItemData

PK | ScanCode | StoreFK | Retail | ReceiptAlias
1  | 100101   | 1       | 4.99   | Milk
4  | 100101   | 2       | 4.99   | Milk
7  | 100101   | 3       | 0.99   | Milk
2  | 100102   | 1       | 6.99   | Eggs
5  | 100102   | 2       | 6.99   | Eggs
8  | 100102   | 3       | 6.99   | Eggs
3  | 100103   | 1       | 7.99   | Bread
6  | 100103   | 2       | 8.99   | Bread
9  | 100103   | 3       | 9.99   | Bread

I can use the following SQL query to get the following return results, which shows all the items that have a different retail at one or more stores: see this previously asked question

SQL Query:

SELECT   im.INV_ScanCode     AS ScanCode,
         name.ItemData AS im
GROUP BY ScanCode, Receipt_Alias
HAVING   COUNT(DISTINCT im.Retail) > 1
ORDER BY ScanCode

Current Return Results:

ScanCode | Receipt_Alias 
100101   | Milk
100103   | Bread

I would like to return all the items that have a different retail at one or more stores AND the retail at each store location:

Desired Return Results:

ScanCode | Receipt_Alias | Str_1 | Str_2 | Str_3
100101   | Milk          | 4.99  | 4.99  | 0.99
100103   | Bread         | 7.99  | 8.99  | 9.99

How would I change my SQL query to include the retail at each store location?

You're looking to transpose rows to columns (pivot). You should try to do this on the application level if possible. — Kermit, Jan 19 '13 at 17:14

John Woo · Accepted Answer · 2013-01-19T17:24:14.973

2

SELECT  ScanCode as ItemID,
        ReceiptAlias,
        MAX(CASE WHEN StoreFK = 1 THEN Retail ELSE NULL END) Store_1,
        MAX(CASE WHEN StoreFK = 2 THEN Retail ELSE NULL END) Store_2,
        MAX(CASE WHEN StoreFK = 3 THEN Retail ELSE NULL END) Store_3
FROM    ItemData
GROUP   BY ScanCode, ReceiptAlias
HAVING  COUNT(DISTINCT Retail) > 1

SQLFiddle Demo

edited Jan 19 '13 at 17:24

answered Jan 19 '13 at 17:14

John Woo

258,903
69
498
492

This returns all the items, not just the items with retail differing at one or more store locations. See the link to the other SO question. – recursion.ninja Jan 19 '13 at 17:19
To prevent return errors, I changed `ELSE NULL END` to `ELSE 0 END`. I suggest future readers do the same! – recursion.ninja Jan 19 '13 at 17:40

Return multiple row's column values as columns of linked item

1 Answers1