Basically this blog post is about dynamic columns in MySQL queries. Since you can't run "pivot" queries in MySQL, how do you write queries that return an arbitrary number of columns (perhaps based on the number of rows in another table)?
The answer is simple: you can't... at least not in a single query. The best way to do this is to run two queries and generate the SQL for the 2nd query based on the results of the 1st query.
Imagine this table of product sales:
Maybe you want your result set to look like this...
The answer is simple: you can't... at least not in a single query. The best way to do this is to run two queries and generate the SQL for the 2nd query based on the results of the 1st query.
Imagine this table of product sales:
| ProductID | CustomerID | Unit Sales |
|---|---|---|
| 1 | 1 | 20 |
| 1 | 2 | 50 |
| 2 | 1 | 20 |
| 2 | 2 | 70 |
| 2 | 3 | 90 |
Maybe you want your result set to look like this...
| ProductID | Customer 1 | Customer 2 | Customer 3 |
|---|---|---|---|
| 1 | 20 | 50 | 0 |
| 2 | 20 | 70 | 90 |
Write this query first:
SELECT CustomerID, CustomerName FROM `customers`
Store the results into $rows. For each $row, append this string to $fields:
", SUM(CASE WHEN CustomerID=$row[0] THEN `Unit Sales` ELSE 0 END) AS $row[1]"
Then... write this query:
"SELECT ProductID $fields
FROM `sales`
GROUP BY ProductID"
Done. This pattern is not recommended, but sometimes you might find this to be useful for custom reporting. Also, I can guarantee that this performs WAY better than multiple joins. Plus, MySQL places a limit on the maximum number of table joins you can have in a query... at the time of this writing, the limit is 61. Cheers!
Comments