0

I have a database table whose important columns are as follows:

CREATE myTable (
  id int,
  type varchar(128),
  field1 text);

What I would like to do is get a count of the number of rows of each type by each user. The SQL for this is straight-forward:

SELECT id, type, count(*) as count
FROM myTable
GROUP BY id, type;

The challenge I am facing is that I would like the output to be in the form as a two-dimensional table, with the rows being id and the columns being the different types (of which, there are a dozen or so).

I have figured out how to create the table structure for this:

SELECT
  CONCAT(
    'CREATE TABLE tmpMyTable (id int,',
    GROUP_CONCAT(DISTINCT
      CONCAT(type, ' int')
      SEPARATOR ','),
    ');')
FROM (select distinct type from myTable) t
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

but I cannot figure out how to efficiently populate the table with data. Yes, I could use a cursor, looping over the column names, and executing a dynamic SQL statement for each column. However, I am wondering if there is an approach that doesn't use a cursor.

Any suggestions?

Thanks.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
GRoston
  • 325
  • 1
  • 3
  • 12
  • If you know all `type` values upfront you can use conditional aggregation for every column. – PM 77-1 Sep 04 '18 at 19:23
  • Bill - Thank you for pointing me to the other answer. Technically, it is not an answer to my question since I posed the question as storing the counts into a table (whereas the answer simply returns the counts as a SELECT). However, for my purposes, that answer suffices, so I am not going to pursue this further. – GRoston Sep 05 '18 at 22:02

0 Answers0