MySql query to pull data in matrix form from database

Question

I have data in database with two columns name and year. Something like this,

name | year
-----------
tim  | 2001
ron  | 2002
tim  | 2003
ron  | 2005
tim  | 2004
pol  | 2002
pol  | 2001
tim  | 2003
pol  | 2004
tim  | 2002

I want to output the result matrix something like this.

      tim | pol | ron
2001   1  |  1  |  0
2002   1  |  1  |  1
2003   2  |  0  |  0
2004   1  |  1  |  0
2005   0  |  0  |  1

tim | pol | ron is arranged in descending order based on cumulative sum i.e tim(5), pol(3), ron(2).

Data is not limited to tim,pol,ron. There can be n-different names.

see stackoverflow.com/questions/7674786/mysql-pivot-table

Ian Kenney
– Ian Kenney

2013-07-01 08:36:06 +00:00
Commented Jul 1, 2013 at 8:36 — Ian Kenney
– Ian Kenney, Commented Jul 1, 2013 at 8:36

fthiella · Accepted Answer · 2013-07-01 08:51:21Z

6

SELECT
  year,
  SUM(name='tim') tim,
  SUM(name='pol') pol,
  SUM(name='ron') ron
FROM
  yourtable
GROUP BY
   year

Please see fiddle here.

EDIT: if you need a dynamic query because the exact number of values could vary, you could use a prepared statement like this:

SELECT
  CONCAT('SELECT year,',
  GROUP_CONCAT(sums),
  ' FROM yourtable GROUP BY year')
FROM (
SELECT CONCAT('SUM(name=\'', name, '\') AS `', name, '`') sums
FROM yourtable
GROUP BY name
ORDER BY COUNT(*) DESC
) s
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Fiddle is here.

edited Jul 1, 2013 at 8:51

answered Jul 1, 2013 at 8:37

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

4 Comments

Zero Over a year ago

Data is not limited to tim,pol,ron. There can be n-different names.

Stephan Over a year ago

@JohnGalt the pivot in mysql is static so you have to build the query yourself using the n columns you need

fthiella Over a year ago

@JohnGalt mysql doesn't have a pivot function, but you can create a dynamic query, please see my updated answer

Zero Over a year ago

@fthiella SQL Fiddle solution is perfect. I tried to recreate this in wamp phpmyadmin and getting an error: #1054 - Unknown column 'year' in 'field list' Any ideas how to fix this?

Noam Rathaus · Accepted Answer · 2013-07-01 08:54:05Z

0

You can do the following then at the presentation layer transpose it.

SELECT yourtable.year, Hits.name, Hits.hit
FROM yourtable
LEFT JOIN (SELECT
 year, name, count(*) as hit
FROM
  yourtable
GROUP BY
   year, name) AS Hits ON yourtable.year = Hits.year
GROUP BY yourtable.year, yourtable.name

It lacks no-hits, i.e. those names that don't fall in a certain year, i.e. you won't see any 0.

answered Jul 1, 2013 at 8:54

Noam Rathaus

5,6082 gold badges31 silver badges37 bronze badges

Comments

RandomSeed · Accepted Answer · 2013-07-01 10:08:19Z

While fthiella's approach is the most common way to build a pivot table, you may want to try this query, which is faster in most cases:

SELECT
  years.year,
  COALESCE(tim.c, 0) AS tim,
  COALESCE(pol.c, 0) AS pol,
  COALESCE(pol.c, 0) AS ron
FROM (SELECT year FROM yourtable GROUP BY year) AS years
LEFT JOIN (
  SELECT COUNT(name) AS c, year FROM yourtable WHERE name = 'tim' GROUP BY year
) AS tim ON (tim.year = years.year)
LEFT JOIN (
  SELECT COUNT(name) AS c, year FROM yourtable WHERE name = 'pol' GROUP BY year
) AS pol ON (pol.year = years.year)
LEFT JOIN (
  SELECT COUNT(name) AS c, year FROM yourtable WHERE name = 'ron' GROUP BY year
) AS ron ON (ron.year = years.year)
GROUP BY years.year;

This is a bit more painful to write, and to perform well, it requires two indexes:

a two-columns index on both fields used as column and row discriminant, in this order (ie. in your case, on (name, year) in this order)
an index on the row discriminant (ie. in your case, on (year))

But the larger the result (either more rows or more columns or both), the faster it performs compared to the usual approach. I posted some sample data here if anyone feels like playing with it. The corresponding queries are here.

Collectives™ on Stack Overflow

MySql query to pull data in matrix form from database

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related