Query with variable number of columns?

[Jan Steinman]

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 ::::

[Peter Brawley]

Post by Jan Steinman
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

-----

Post by Jan Steinman
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

[h***@tbbs.net]

2014/10/08 11:38 -0700, Jan Steinman >>>>

However, this pattern will often result in numerous empty columns -- empties that would not be there had the table not been pivoted.
<<<<<<<<

2014/10/08 16:42 -0500, Peter Brawley >>>>

MySQL stored procedures are less incomplete, and can do it, but they're awkward.
<<<<<<<<
From a webpage-link on this very list posted, I learnt of a means of (yes, clumsily) using SQL procedure to build PREPAREd statements that pivot. It entails twice reckoning, once to find good fields, once to pivot and show them.

One selects from a virtual table:
(SELECT DISTINCT YEAR(sales.Date) AS Y FROM ...) AS g

For each good Y one wants this generated (I use ANSI mode, with more PL1 than C):
'SUM(IF(YEAR(sales.Date)=' || Y || ', sales.Total, NULL)) AS "' || Y || '"'

The outcome is something like this:
set @yearSal = (SELECT 'SELECT s_product.name AS "Product", ' || GROUP_CONCAT('SUM(IF(YEAR(sales.Date)=' || Y || ', sales.Total, NULL)) AS "' || Y || '"') || '
FROM ...'
FROM (SELECT DISTINCT YEAR(sales.Date) AS Y FROM ...) AS g;
PREPARE YearSal FROM @YearSal;
EXECUTE YearSal;

Unhappily, PREPARE takes only user-defined variables, and its prepared statement, too, is exposed to the procedure s caller. If the prepared statement is "SELECT ... INTO ...", only user-defined variables are allowed after "INTO". One who knows the names can learn something about the procedure s working.