>>>> 2012/11/19 05:05 +0100, Mogens Melander >>>>
I found an article on:
http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab
Describing how to do the dynamic generation of SQL statements.
<<<<<<<<
And I was inspired to do some such thing to one of my views.
This view has three fields:
"City-ZIP": a string of 5-digit ZIP-code and place-name;
Hoads: one of 11 distinct number from 1 through 7.5, showing how good the member is;
Members: howmany so good members dwell at that place.
It is more convenient to show this in a table with City-ZIP at the left, HoadS across the
top, and Members throughout the middle. OpenOffice Calc has the needed operation, and I
regularly used it for making the table. But with this, I can do much of it in MySQL (no
row totals):
SELECT 'SELECT "City-ZIP", ' || GROUP_CONCAT('SUM(IF(HoadS = ' || HoadS || ', Members,
NULL)) AS "' || HoadS || '"') || '
FROM ZIPbwise
GROUP BY "City-ZIP" WITH ROLLUP'
FROM (SELECT HoadS FROM zipbwise GROUP BY HoadS) AS g
It yields this query:
SELECT "City-ZIP",
SUM(IF(HoadS = 1.0, Members, NULL)) AS "1.0",
SUM(IF(HoadS = 1.5, Members, NULL)) AS "1.5",
SUM(IF(HoadS = 2.0, Members, NULL)) AS "2.0",
SUM(IF(HoadS = 3.0, Members, NULL)) AS "3.0",
SUM(IF(HoadS = 4.0, Members, NULL)) AS "4.0",
SUM(IF(HoadS = 4.5, Members, NULL)) AS "4.5",
SUM(IF(HoadS = 5.0, Members, NULL)) AS "5.0",
SUM(IF(HoadS = 5.5, Members, NULL)) AS "5.5",
SUM(IF(HoadS = 6.5, Members, NULL)) AS "6.5",
SUM(IF(HoadS = 7.0, Members, NULL)) AS "7.0",
SUM(IF(HoadS = 7.5, Members, NULL)) AS "7.5"
FROM ZIPbwise GROUP BY "City-ZIP" WITH ROLLUP
