0

I have a table like this,

+----------------+
| id | name|type |
+----+-----+-----+
| 1  |  J  |  A  |
| 2  |  S  |  A  |
| 3  |  S  |  B  |
| 4  |  S  |  B  |
| 5  |  J  |  B  |
| 6  |  J  |  C  |
+----+-----+-----+

id is Primary Key, name's value are not certain, but type's value can only be A, B or C. Want to build a MySql view, which means a SELECT, to output following table, which count different types by name.

+--------------------------------------------------+
| name| countOfTypeA | countOfTypeB | countOfTypeC |
+--------------------------------------------------+
|  J  |  1           | 1            |  1           |
+--------------------------------------------------+
|  S  |  1           | 2            |  0           |
+--------------------------------------------------+

Tried "Group by name, type" but it output types in rows not in columns. Please help.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
brewphone
  • 1,316
  • 4
  • 24
  • 32
  • Consider handling issues of data display in application code – Strawberry May 14 '19 at 22:08
  • Exactly as what Bill Karwin pointed out, the answer of question "MySQL pivot row into dynamic number of columns" did answer my question. Thanks. – brewphone May 15 '19 at 02:34
  • select t.n, count(case when t.t = 'A' THEN 1 END) A, count(case when t.t = 'B' THEN 1 END) B, count(case when t.t = 'C' THEN 1 END) C FROM test as t group by t.n; – brewphone May 15 '19 at 02:41

0 Answers0