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 http://stackoverflow.com/questions/7674786/mysql-pivot-table — Ian Kenney, Jul 01 '13 at 08:36

fthiella · Accepted Answer · 2013-07-01T08:51:21.040

5

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 01 '13 at 08:51

answered Jul 01 '13 at 08:37

fthiella

48,073
15
90
106

Data is not limited to tim,pol,ron. There can be n-different names. – Zero Jul 01 '13 at 08:39
@JohnGalt the pivot in mysql is static so you have to build the query yourself using the n columns you need – Stephan Jul 01 '13 at 08:40
@JohnGalt mysql doesn't have a pivot function, but you can create a dynamic query, please see my updated answer – fthiella Jul 01 '13 at 08:52
@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? – Zero Jul 01 '13 at 20:53

score 0 · Answer 2 · answered Jul 01 '13 at 08:54

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.

score 0 · Answer 3 · answered Jul 01 '13 at 10:08

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.

MySql query to pull data in matrix form from database

3 Answers3