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

Answer 1

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.

Answer 2

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

Answer 3

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).

Answer 4

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.