SELECT rows as columns in MySQL?

Question

Consider the following table "views"

user_id  _date       cnt
------------------------
1        2011-02-10  123
1        2011-02-11   99
1        2011-02-12  100
1        2011-02 13   12
2        2011-02-10   32
2        2011-02-11  433
2        2011-02-12  222
2        2011-02 13  334
3        2011-02-10  766
3        2011-02-11  654
3        2011-02-12   43
3        2011-02 13   27
...
100      2011-02-13  235

As you can see, the table holds pageviews (cnt) per day (_date) per user (user_id). I'm looking for a SELECT query that will output the user_ids as columns so the table-data will be in matrix form, as follows:

_date         1    2    3 ... 100
---------------------------------
2011-02-10  123   32  766
2011-02-11   99  433  654
2011-02-12  100  222   43
2011-02-13   12  334   27     235

Is this possible to do with a SELECT statement?

I don't think this is possible in MySQL as it does not include a "pivot" command ... might want to think about implementing this logic to your application layer. — McGarnagle, Apr 07 '12 at 23:39
Are you looking for a fixed amount of columns (for a fixed set of `user_id`) or for every `user_id` in the table? — Mosty Mostacho, Apr 07 '12 at 23:41
A fixed amount. There are one hundred user_id's. See the OP edit :-) — Pr0no, Apr 07 '12 at 23:52
answered here? http://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when — Tahbaza, Apr 08 '12 at 00:02

benzado · Accepted Answer · 2012-04-09T20:32:17.557

5

If you're dealing with a finite set of user IDs, you could do something like this:

SELECT _date,
    SUM(CASE WHEN _user_id = 1 THEN cnt ELSE 0 END) AS user1,
    SUM(CASE WHEN _user_id = 2 THEN cnt ELSE 0 END) AS user2,
    SUM(CASE WHEN _user_id = 3 THEN cnt ELSE 0 END) AS user3,
    ...
FROM views
GROUP BY _date

It's more of a hack than a good query, though.

edited Apr 09 '12 at 20:32

answered Apr 08 '12 at 00:01

benzado

82,288
22
110
138

1

Indeed, and expect slow queries if you're working with a large dataset. Best way around it though! – BenOfTheNorth Apr 08 '12 at 00:03
Thanks! However, this query gives me an error: `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS user1, SUM(CASE WHEN user_id = 2 THEN cnt ELSE 0) AS user2, ' at line 3` Any suggestions as to why this happens? – Pr0no Apr 09 '12 at 12:01
@Pr0no I left out the `END`; the example is fixed now. You could have figured that out yourself if you looked up `CASE` in the MySQL documentation. – benzado Apr 09 '12 at 20:33

score 1 · Answer 2 · answered Oct 01 '12 at 21:10

It looks like you have an long list of values that you want to transform. If that is the case then you can use prepared statements. Your code will look like this (see SQL Fiddle with Demo):

CREATE TABLE Table1
    (`user_id` int, `_date` datetime, `cnt` int)
;

INSERT INTO Table1
    (`user_id`, `_date`, `cnt`)
VALUES
    (1, '2011-02-09 17:00:00', 123),
    (1, '2011-02-10 17:00:00', 99),
    (1, '2011-02-11 17:00:00', 100),
    (1, '2011-02-13 00:00:00', 12),
    (2, '2011-02-09 17:00:00', 32),
    (2, '2011-02-10 17:00:00', 433),
    (2, '2011-02-11 17:00:00', 222),
    (2, '2011-02-13 00:00:00', 334),
    (3, '2011-02-09 17:00:00', 766),
    (3, '2011-02-10 17:00:00', 654),
    (3, '2011-02-11 17:00:00', 43),
    (3, '2011-02-13 00:00:00', 27),
    (100, '2011-02-12 17:00:00', 235)
;

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when user_id = ''',
      user_id,
      '''  then cnt else 0 end) AS ''',
      user_id, ''''
    )
  ) INTO @sql
FROM Table1;

SET @sql = CONCAT('SELECT _Date, ', @sql, ' 
                  FROM table1 
                  GROUP BY _Date');

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

SELECT rows as columns in MySQL?

2 Answers2