SELECT rows as columns in MySQL?
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 contains pageviews (cnt) per day (_date) for each user (user_id). I am looking for a SELECT query that will output user_ids as columns so that the table data is in matrix form as shown below:
_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
Can this be done with a SELECT statement?
+3
source to share
2 answers
If you are dealing with a finite set of user IDs, you can 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
This is more of a hack than a good query.
+4
source to share
It looks like you have a long list of values that you want to convert. If so, 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;
0
source to share