SQL: Expand 1 Column into 3 on Summarize Table

Question

I'm writing a program that displays products and the stores at which they can be located.

So far, I've been able to write an SQL query that displays data as follows:

Product    Availability
Milk       Store1
Candy      Store1
Eggs       Store1
Milk       Store2
Eggs       Store2
Candy      Store3
Eggs       Store3

Is there any SQL query I can use to have the products grouped on the Product column and the stores at which they can be located expanded into 3 columns as illustrated below?

Product  Store1  Store2  Store3
Milk     Yes     Yes     No
Candy    Yes     No      Yes
Eggs     Yes     Yes     Yes

score 4 · Accepted Answer · answered Oct 12 '10 at 13:57

4

SELECT 
    Product,
    MAX(CASE WHEN Availability='Store1' THEN 'Yes' ELSE 'No' END) AS Store1,
    MAX(CASE WHEN Availability='Store2' THEN 'Yes' ELSE 'No' END) AS Store2,
    MAX(CASE WHEN Availability='Store3' THEN 'Yes' ELSE 'No' END) AS Store3
FROM YourTable
GROUP BY Product

answered Oct 12 '10 at 13:57

Martin Smith

438,706
87
741
845

A good solution, but limited by the number of stores in this example. If new store comes up, the query will need update. @Martin: can't we make it dynamic? – Kangkan Oct 12 '10 at 14:02
@Kangkan - Depends on DBMS. Access has a `transform` operator. SQL Server would require the whole query string to be generated dynamically. – Martin Smith Oct 12 '10 at 14:04
Thanks, Martin. Since the database is in Access, I used `IIf` instead of `CASE WHEN` and it worked. – Alex Essilfie Oct 12 '10 at 15:21
@Alex - If you have the need to generate the columns dynamically when you add new stores then check out the [Transform Statement](http://office.microsoft.com/en-us/access-help/transform-statement-HP001032277.aspx) in Access. – Martin Smith Oct 12 '10 at 15:23
@Kangkan - The number of stores are not going to change, EVER so this query is adequate. Just so I may know, Is there any other query which would do this dynamically [as in for any number of stores]? – Alex Essilfie Oct 12 '10 at 15:23
if it is Access you can create a PIVOT query and get the results in the correct format – Leslie Oct 12 '10 at 17:46

score 3 · Answer 2 · answered Oct 12 '10 at 13:56

3

A very common question. The word you need to know is pivot. Search StackOverflow for "pivot", or maybe "cross tab" to find a lot of discussions and examples.

answered Oct 12 '10 at 13:56

Philip Kelley

39,426
11
57
92

Donnie · Answer 3 · 2010-10-12T15:07:36.967

3

Most RDBMs support pivot nowadays. It would help a lot if you specified which database you're using. Some examples: MSSQL, postgres (the crosstab function) and so on.

edited Oct 12 '10 at 15:07

answered Oct 12 '10 at 14:01

Donnie

45,732
10
64
86

1

Copy and paste fail. Thanks. – Donnie Oct 12 '10 at 15:07

score 1 · Answer 4 · answered Oct 12 '10 at 14:01

1

Presuming that you don't know the column names before running the query, this blog gives a good solution (but watch out for injection attacks).

answered Oct 12 '10 at 14:01

smirkingman

6,167
4
34
47

SQL: Expand 1 Column into 3 on Summarize Table

4 Answers4