I often use CASE WHEN ... to pivot tables. For example, splitting sales data by
year:
SELECT
s_product.name AS `Product`,
SUM(CASE WHEN YEAR(sales.Date)='2007' THEN sales.Total ELSE NULL END) AS
`2007`,
SUM(CASE WHEN YEAR(sales.Date)='2008' THEN sales.Total ELSE NULL END) AS
`2008`,
SUM(CASE WHEN YEAR(sales.Date)='2009' THEN sales.Total ELSE NULL END) AS
`2009`,
...
WHERE dynamic predicate that only has results in one year
However, this pattern will often result in numerous empty columns -- empties
that would not be there had the table not been pivoted.
What techniques do *you* use for avoiding this anti-pattern? Am I limited to
using a separate programming language (PHP, in this case) with a separate
COUNT(*) query for each possible column, then CASEing the generation of the
column SQL? Seems awfully ugly!
Thanks in advance for any insight offered!
(And the following came up at random... perhaps I'll just live with a bunch of
empty columns...)
:::: In attempting to fix any system, we may damage another that is working
perfectly well. -- David Holmgren
:::: Jan Steinman, EcoReality Co-op ::::
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
