MySQL cross tabulation implementation _ MySQL

The MySQL cross tabulation implements sharing bitsCN.com. The current solution is as follows:

Data sample:

Create table tx (
Id int primary key,
C1 char (2 ),
C2 char (2 ),
C3 int
);

Insert into tx values
(1, 'A1', 'b1 ', 9 ),
(2, 'A2 ', 'b1', 7 ),
(3, 'A3 ', 'b1', 4 ),
(4, 'A4 ', 'b1', 2 ),
(5, 'A1', 'B2', 2 ),
(6, 'A2 ', 'B2', 9 ),
(7, 'A3 ', 'B2', 8 ),
(8, 'A4 ', 'B2', 5 ),
(9, 'A1', 'b3', 1 ),
(10, 'A2 ', 'b3', 8 ),
(11, 'A3 ', 'b3', 8 ),
(12, 'A4 ', 'b3', 6 ),
(13, 'A1', 'B4 ', 8 ),
(14, 'A2 ', 'B4', 2 ),
(15, 'A3 ', 'B4', 6 ),
(16, 'A4 ', 'B4', 9 ),
(17, 'A1', 'B4 ', 3 ),
(18, 'A2 ', 'B4', 5 ),
(19, 'A3 ', 'B4', 2 ),
(20, 'A4 ', 'B4', 5 );

Mysql> select * from tx;
+ ---- + ------ +
| Id | c1 | c2 | c3 |
+ ---- + ------ +
| 1 | A1 | B1 | 9 |
| 2 | A2 | B1 | 7 |
| 3 | A3 | B1 | 4 |
| 4 | A4 | B1 | 2 |
| 5 | A1 | B2 | 2 |
| 6 | A2 | B2 | 9 |
| 7 | A3 | B2 | 8 |
| 8 | A4 | B2 | 5 |
| 9 | A1 | B3 | 1 |
| 10 | A2 | B3 | 8 |
| 11 | A3 | B3 | 8 |
| 12 | A4 | B3 | 6 |
| 13 | A1 | B4 | 8 |
| 14 | A2 | B4 | 2 |
| 15 | A3 | B4 | 6 |
| 16 | A4 | B4 | 9 |
| 17 | A1 | B4 | 3 |
| 18 | A2 | B4 | 5 |
| 19 | A3 | B4 | 2 |
| 20 | A4 | B4 | 5 |
+ ---- + ------ +
20 rows in set (0.00 sec)

Mysql>

Expected results

+ ------ + ----- + ------ +
| C1 | B1 | B2 | B3 | B4 | Total |
+ ------ + ----- + ------ +
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| Total | 22 | 24 | 23 | 40 | 109 |
+ ------ + ----- + ------ +

1. use SUM (IF () to generate a column + with rollup to generate a summary row, and use IFNULL to display the title of the summary row as Total

Mysql> SELECT
-> IFNULL (c1, 'total') AS total,
-> SUM (IF (c2 = 'b1 ', c3, 0) AS B1,
-> SUM (IF (c2 = 'B2', c3, 0) AS B2,
-> SUM (IF (c2 = 'b3', c3, 0) AS B3,
-> SUM (IF (c2 = 'B4 ', c3, 0) AS B4,
-> SUM (IF (c2 = 'total', c3, 0) AS total
-> FROM (
-> SELECT c1, IFNULL (c2, 'total') AS c2, SUM (c3) AS c3
-> FROM tx
-> Group by c1, c2
-> WITH ROLLUP
-> HAVING c1 IS NOT NULL
->) AS
-> Group by c1
-> With rollup;
+ ------- + ------ + ------- +
| Total | B1 | B2 | B3 | B4 | total |
+ ------- + ------ + ------- +
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| Total | 22 | 24 | 23 | 40 | 109 |
+ ------- + ------ + ------- +
5 rows in set, 1 warning (0.00 sec)

2. use SUM (IF () to generate a column + UNION to generate a summary row, and use IFNULL to display the title of the summary row as Total
Mysql> select c1,
-> Sum (if (c2 = 'b1 ', C3, 0) AS B1,
-> Sum (if (c2 = 'B2', C3, 0) AS B2,
-> Sum (if (c2 = 'b3', C3, 0) AS B3,
-> Sum (if (c2 = 'B4 ', C3, 0) AS B4, SUM (C3) AS TOTAL
-> From tx
-> Group by C1
-> UNION
-> SELECT 'total', sum (if (c2 = 'b1 ', C3, 0) AS B1,
-> Sum (if (c2 = 'B2', C3, 0) AS B2,
-> Sum (if (c2 = 'b3', C3, 0) AS B3,
-> Sum (if (c2 = 'B4 ', C3, 0) AS B4, SUM (C3) FROM TX
->;
+ ------- + ------ + ------- +
| C1 | B1 | B2 | B3 | B4 | TOTAL |
+ ------- + ------ + ------- +
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| TOTAL | 22 | 24 | 23 | 40 | 109 |
+ ------- + ------ + ------- +
5 rows in set (0.00 sec)

Mysql>

3. generate columns using SUM (IF () and generate results directly without subqueries
Mysql> select ifnull (c1, 'total '),
-> Sum (if (c2 = 'b1 ', C3, 0) AS B1,
-> Sum (if (c2 = 'B2', C3, 0) AS B2,
-> Sum (if (c2 = 'b3', C3, 0) AS B3,
-> Sum (if (c2 = 'B4 ', C3, 0) AS B4, SUM (C3) AS TOTAL
-> From tx
-> Group by C1 with rollup;
+ -------------------- + ------ + ------- +
| Ifnull (c1, 'total') | B1 | B2 | B3 | B4 | total |
+ -------------------- + ------ + ------- +
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| Total | 22 | 24 | 23 | 40 | 109 |
+ -------------------- + ------ + ------- +
5 rows in set (0.00 sec)

Mysql>

4. dynamic, applicable to column uncertainties,
Mysql> SET @ EE = '';
Mysql> SELECT @ EE: = CONCAT (@ EE, 'sum (IF (C2 =/'', C2, '/'',', C3, 0) ', c2, ',') FROM (select distinct C2 from tx);

Mysql> SET @ QQ = CONCAT ('select ifnull (c1,/'total/'),', LEFT (@ EE, LENGTH (@ EE)-1 ),', SUM (C3) as total from tx group by C1 with rollup ');
Query OK, 0 rows affected (0.00 sec)

Mysql> PREPARE stmt2 FROM @ QQ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Mysql> EXECUTE stmt2;
+ -------------------- + ------ + ------- +
| Ifnull (c1, 'total') | B1 | B2 | B3 | B4 | total |
+ -------------------- + ------ + ------- +
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| Total | 22 | 24 | 23 | 40 | 109 |
+ -------------------- + ------ + ------- +
5 rows in set (0.00 sec)
Mysql>

All of the above are by netizensLiangCK, wwwwb, WWWWA, dap570Yes. Thanks again for their support.
In fact, the database can also use case when/DECODE to replace IF

BitsCN.com