>>>> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
