List:General Discussion« Previous MessageNext Message »
From:hsv Date:November 20 2012 4:33am
Subject:Re: Dynamic crosstab got me lost.
View as plain text  
>>>> 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

Thread
Dynamic crosstab got me lost.Mogens Melander14 Nov
  • RE: Dynamic crosstab got me lost.Rick James14 Nov
  • RE: Dynamic crosstab got me lost.Mogens Melander14 Nov
    • Re: Dynamic crosstab got me lost.Peter Brawley15 Nov
  • Re: Dynamic crosstab got me lost.Mogens Melander19 Nov
    • Re: Dynamic crosstab got me lost.hsv20 Nov
Re: Dynamic crosstab got me lost.Jan Steinman19 Nov
  • Re: Dynamic crosstab got me lost.Mogens Melander19 Nov
    • Re: Dynamic crosstab got me lost.Shawn Green19 Nov
      • Re: Dynamic crosstab got me lost.Mogens Melander20 Nov
  • Re: Dynamic crosstab got me lost.hsv19 Nov