MySQL select count of distinct values into seperate columns

Question

I want to get the count of rows for each distinct value in column A grouped by column B like this:

------------------------------------------------------
| B | AValue1 | AValue2 | Avalue3 | AValue4 | ......  |
------------------------------------------------------
|B1 |    x    |    x    |    x    |    x    |    x    |
|B2 |    x    |    x    |    x    |    x    |    x    |
|...|    x    |    x    |    x    |    x    |    x    |
-------------------------------------------------------

x being the different counts. Right now i am getting basically the same data using "Group By(A,B)" but it is in the form:

----------------------------------------------
| A         |    B     |   Count             |
----------------------------------------------    
| AValue1   | BValue1  |     x               |
| ...       | ....     |     x               |
----------------------------------------------

after which i have to transform the Data in PHP or on the client in Javascript. The only way i could come up with to get the first table would be to do a subquery select for each of the values in A, but that defeats the purpose of it being a simpler and cleaner solution. Is there an easy way to achieve this in SQL or is transformation of the Group By table the best approach? Thanks in Advance

So if `A` column has a thousand distinct values, you will want a thousand columns? — BeetleJuice, Aug 30 '17 at 13:17
https://stackoverflow.com/questions/7674786/mysql-pivot-table — GolezTrol, Aug 30 '17 at 13:17
@BeetleJuice theoretically yes, although in this case i can be sure that the column will ever only have somewhere around 30 distinct values — RagingPixels, Aug 30 '17 at 13:55

score 0 · Accepted Answer · answered Aug 30 '17 at 15:32

@GolezTrol's comment pointed me in the right direction. MySQL sadly doesnt have PivotTables, but i managed to solve it using the following group concat statement:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(B = ''',
      B,
      ''', 1, 0)) AS `',
      B,
      '`'
    )
  ) INTO @sql
FROM table
SET @sql = CONCAT('SELECT A, ', @sql, ' FROM table GROUP BY A');

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

MySQL select count of distinct values into seperate columns

1 Answers1