Convert Row data to column in MySQL

Question

I have MySQL table which 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
.......

Type column can hold either of 3 values 1,3,99. Month will hold values from 1 to 12. Count can be anything random.

The output I desire is something like this:

Month|Type1|Type3|Type99
-----+-----+-----+-------
9    |4    |7    |2
10   |6    |7    |9
................

I came across this Demo but couldn't understand much from it.

Here's a sample fiddle with demo data.

Any help is appreciated.

At least create a fiddle with your sample data.

Mihai
– Mihai

2014-10-12 19:39:08 +00:00
Commented Oct 12, 2014 at 19:39 — Mihai
– Mihai, Commented Oct 12, 2014 at 19:39
added link to fiddle with sample data

Nilesh Barai
– Nilesh Barai

2014-10-12 19:50:40 +00:00
Commented Oct 12, 2014 at 19:50 — Nilesh Barai
– Nilesh Barai, Commented Oct 12, 2014 at 19:50

Abhishek Gupta · Accepted Answer · 2014-10-12 20:15:30Z

8

Try below query, what you need is know as MYSQL pivot and below query solves your issue

STATIC WAY

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;

edited Oct 12, 2014 at 20:15

answered Oct 12, 2014 at 19:56

Abhishek Gupta

4,21626 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Abhishek Gupta Over a year ago

@Nilesh Barai, was this helpful ?

Nilesh Barai Over a year ago

Its not working. It shows values 0 for type3 and type99 columns

Nilesh Barai Over a year ago

this is what i need. Simple and elegant!!

Abhishek Gupta Over a year ago

@Mihai: You are not the only one who know mysql, please refer stackoverflow.com/questions/26263917/…

Abhishek Gupta Over a year ago

@Mihai: I did my copy paste from my own concept, refer that link.

Collectives™ on Stack Overflow

Convert Row data to column in MySQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related