Search code examples
mysqlsqljoinalter

Creating a virtual table in MySQL

So from a number of tables in my database I'm joining them together to get a virtual table to return to my nodejs program. It comes out something like this:

Project | OS   |   On
A         Win     False       
A         Mac      True
A         Mac      True
B         Win      True
B         Win      True
C         OS       False

My question is; what is the easiest way to convert the above table into a virtual table like this.

Project | Win | Mac | True | False
   A        1    3      2      1
   B        2    0      2      0
   C        0    1      0      1

Should this be done in the mysql query, or should this be done once the initial table is returned to my program and then edited in there?

Solution

  • You can do this in SQL:

    SELECT Project, 
  COUNT(CASE OS WHEN 'Win' THEN 1 END) AS Win,
  COUNT(CASE OS WHEN 'Win' THEN 1 END) AS Mac,
  COUNT(CASE `On` WHEN 'True' THEN 1 END) AS `True`,
  COUNT(CASE `On` WHEN 'False' THEN 1 END) AS `False`
FROM NoOneEverNamesTheirTableInSqlQuestions
GROUP BY Project