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