On 2014-10-08 1:38 PM, Jan Steinman wrote:
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?
Non-procedural SQL is an incomplete computer language; it can't do that. MySQL stored procedures are less incomplete, and can do it, but they're awkward. I use the app language (eg PHP) to implement such logic.
PB -----
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
