Convert row data to MySQL column
I have a MySQL table that stores data in this format
Type|Month|Count
----+-----+-------
1 |9 |4
3 |9 |7
99 |9 |2
1 |10 |6
3 |10 |7
99 |10 |9
.......
A typical column can contain any of 3 values ββ1,3,99. The month will contain values ββfrom 1 to 12. Count can be any random.
The result I want is something like this:
Month|Type1|Type3|Type99
-----+-----+-----+-------
9 |4 |7 |2
10 |6 |7 |9
................
I stumbled upon this one but couldn't figure it out.
Here's a fiddle example with demo data.
Any help is appreciated.
+3
source to share
1 answer
Try below query, what you need knows how mySQL pivot and below query solves your problem.
STATIC PATH
SELECT Month,
SUM(CASE WHEN Type = 1 THEN 'count' ELSE 0 END) AS Type1,
SUM(CASE WHEN Type = 3 THEN 'count' ELSE 0 END) AS Type3,
SUM(CASE WHEN Type = 99 THEN 'count' ELSE 0 END) AS Type99
FROM my_table
GROUP BY Month
DYNAMIC WAY
use GROUP_CONCAT with CONCAT
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(CASE WHEN Type= ',
Type, ' THEN count ELSE 0 END) AS '
, 'Type', Type))
INTO @sql
FROM
my_table;
SET @sql = CONCAT('SELECT Month, ', @sql, '
FROM my_table
GROUP BY Month');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
+3
source to share