MySQL row and column conversions

Transfer from http://blog.chinaunix.net/uid-7692530-id-2567582.html

In some databases there is a crosstab, but there is no such function in MySQL, but there are many friends on the Internet to find a solution, special post set the broad sense.
http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198
Now the solution is as follows:

Data samples:

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),
(Ten, ' A2 ', ' B3 ', 8),
(One, ' A3 ', ' B3 ', 8),
(A, ' A4 ', ' B3 ', 6),
(The ' A1 ', ' B4 ', 8),
(+, ' A2 ', ' B4 ', 2),
(A3 ', ' B4 ', 6),
(+, ' A4 ', ' B4 ', 9),
(+, ' A1 ', ' B4 ', 3),
(A2 ', ' B4 ', 5),
(+, ' A3 ', ' B4 ', 2),
(A, ' 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 |
+----+------+------+------+
Rows in Set (0.00 sec)

Mysql>

Expected results

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

1. Use SUM (IF ()) to generate a column + with ROLLUP to generate a total row and use Ifnull to display the summary row header 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
and have C1 is not NULL
) as A
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 total row, and use Ifnull to display the summary row header 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. Using sum (IF ()) to generate a column, directly generating the result no longer using 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)) as ', C2, ', ') from (SELECT DISTINCT C2 from TX) A;

Mysql> SET @QQ =concat (' SELECT ifnull (c1,\ ' total\ '), ', left (@EE, LENGTH (@EE)-1), ', SUM (C3) as total from TX GROUP by C1 WI TH 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>

The above are by netizens Liangck, WWWWB, WWWWA, dap570 provide, again thank them for their support.

In fact, the database can also use case When/decode instead of IF

MySQL row and column conversions