CODEWITHSUNDEEP
mysqlmatrixpivot-table
Zero
Zero

Reputation: 76927

Mysql query to pull data in the form of matrix

I have data in database with three columns id, topic, and subtopic. Something like this,

CREATE TABLE Table2 (`id` int, `topic` varchar(5), `subtopic` varchar(6));
INSERT INTO Table2 (`id`, `topic`, `subtopic`) VALUES
    (1, 'place', 'paris'),
    (1, 'group', 'A'),
    (1, 'group', 'B'),
    (2, 'place', 'us'),
    (2, 'group', 'C'),
    (3, 'group', 'A'),
    (3, 'water', 'salt'),
    (4, 'water', 'sweet'),
    (4, 'world', 'ep'),
    (5, 'place', 'venus'),
    (5, 'place', 'paris'),
    (5, 'group', 'A');

I want to output the result matrix place vs group from topic. Something like this.

    Paris|US|Venus
A   2    |0 | 1
B   1    |0 | 0
C   0    |1 | 0

Idea is to pick up all the values of 'Group' (A,B,C) and 'Places' (paris,us,venus) in subtopic column. And then find number of co-occurrences with such condition.

Any idea how to solve in MySql?

Upvotes: 0

Views: 3141

Answers (1)

Taryn
Taryn

Reputation: 247720

You will need to join on your table twice to get the group and places, then you can use an aggregate function with a CASE expression to convert the rows into columns:

select g.subtopic as `group`,
  sum(case when p.subtopic = 'Paris' then 1 else 0 end) Paris,
  sum(case when p.subtopic = 'US' then 1 else 0 end) US,
  sum(case when p.subtopic = 'Venus' then 1 else 0 end) Venus
from table2 g
left join table2 p
  on g.id = p.id
  and p.topic = 'place'
where g.topic = 'group'
group by g.subtopic;

See SQL Fiddle with Demo.

If you are going to have unknown values for the subtopic, then you can use a prepared statement and dynamic SQL:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when p.subtopic = ''',
      subtopic,
      ''' then 1 else 0 end) as `', 
      subtopic, '`')
  ) INTO @sql
FROM table2
where topic = 'place';

SET @sql = CONCAT('SELECT g.subtopic as `group`, ', @sql, ' 
                  from table2 g
                  left join table2 p
                    on g.id = p.id
                    and p.topic = ''place''
                  where g.topic = ''group''
                  group by g.subtopic');

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

See SQL Fiddle with Demo

Upvotes: 3

Related Questions