Mysql row/column conversion (cross tabulation)

Create two tables, one t_shuiguo fruit table, and one t_supermarket supermarket table. Now I want to check the prices of fruits in each area of a supermarket as follows: in Table A, the first reason is that the fruit table can be dynamically added to all columns in Table A is dynamic. First, let's take A look at the static table. If the four fruit tables are, then the SQL statements can be written like this (refer to some online column) -- Static SQL 01 select ifnull (groups, 'Total') as groups, 02 03sum (if (name = 'apple', prices, 0) as 'apple ', 04 www.2cto.com 05 sum (if (name = 'lil', prices, 0) as 'lil', 06 07sum (if (name = 'orange', prices, 0 )) as 'orange', 08 09sum (if (name = 'cherries ', prices, 0) as 'cherries', 10 11sum (if (name = 'Total', prices, 0) as 'totals' 12 13from14 15 (select. groups as groups, IFNULL (. name, 'Total') as name, sum (. price) as prices16 17from18 19 (select20 21m. groups as groups, s. name as name, m. price as price22 23 from t_supermarket m24 25 inner join t_shuiguo s26 27on m. shuiguo = s. id28 www.2cto.com 29) A30 31 group by groups, name32 33 with rollup34 35 having groups is not null36 37) B38 39 group by groups40 41 with rollup
Then it is difficult to dynamically use the stored procedure as follows: 001 -- Define the Stored procedure Terminator 002 003 delimiter $004 005 -- delete the stored procedure before creating 006 007 drop procedure if exists searchShuiguo $008 009 create procedure searchShuiguo () 010 011begin012 013 -- Define SQL front-end 014 015 declare v_1 varchar (1000) default 'selectifnull (groups, \ 'total \ ') as groups '; 016 017 -- Define SQL backend 018 019 declare v_2 varchar (1000) default 'from (select groups, IFNULL (code, \ 'total \') as code, sum (. price) as prices www.2cto.com 020 021 from (022 023selectm. groups as groups, s. code as code, m. price as price024 025 from t_supermarket m inner join t_shuiguo s on m. shuiguo = s. id026 027) A028 029 group by groups, 030 031 code with rollup having groups is not null032 033) B034 035 group by groups036 037 with rollup '; 038 039 -- define temporary parameters 040 041 declare v_temp varchar (2000); 042 043 -- Define the variable 044 www.2cto.com 045 declare v_shuiguo varchar (100 ); 046 047 -- Define the end variable 048 049 declare stop int default 0; 050 051 -- Define the cursor to query the fruit list 052 053 declare cur cursor for select code from t_shuiguo; 054 055 -- an unfound callback setting 056 057 declare continue handler for not found set stop = 1; 058 059 -- cursor traversal and splicing SQL string 060 061 OPEN cur; 062 063 FETCH cur INTO v_shuiguo; 064 www.2cto.com 065 WHILE stop = 0 066 067DO068 069 if v_temp = ''then070 071 set v_temp = CONCAT (v_1, 'sum (if (code = \ '', v_shuiguo, '\''); 072 073 set v_1 = CONCAT (v_temp,', prices, 0) ', v_shuiguo); 074 075else076 077 set v_temp = ''; 078 079 set v_temp = CONCAT (v_1, ',', 'sum (if (code = \'', v_shuiguo, '\ '',''); 080 081 set v_1 = CONCAT (v_temp,', prices, 0) as ', v_shuiguo); 082 www.2cto.com 083end if; 084 085 FETCH cur INTO v_shuiguo; 086 087 end while; 088 089 CLOSE cur; 090 091 set @ v_result = CONCAT (v_1 ,', sum (if (code = \ 'total \ ', prices, 0) as \ 'total \ ''); 092 093 set @ v_result = CONCAT (@ v_result, v_2 ); 094 095 -- run sql096 097 prepare stmt from @ v_result; 098 099 EXECUTE stmt; 100 www.2cto.com 101 deallocate prepare stmt; 102 103end $ Oh call searchShuiguo () you can try adding a new fruit cantaloupe.
Author's night breeze