Pivot / Cross table report of dates and user entries

Question

Structure/Schema

Create:

CREATE TABLE user_entries
(`id` int, `user_id` int, `form_date` datetime, 
 `user_name` varchar(100));

Some data:

INSERT INTO user_entries
    (`id`, `user_id`, `form_date`, `user_name`)
VALUES
    (1, 100, '2016-03-01', 'User A'),
    (2, 200, '2016-03-02', 'User B'),
    (3, 300, '2016-03-03', 'User C'),
    (4, 100, '2016-03-04', 'User A'),
   (5, 200, '2016-03-05', 'User B');

What I am trying

Dates as columns and users as rows, count for each user entries.

Output should be like:

[User, '2016-03-01', '2016-03-02', '2016-03-03', '2016-03-04', '2016-03-05']
----------------------------------------------------------------------------

[User A,     1     ,      0      ,      0      ,       1      ,       0    ]
[User B,     0     ,      1      ,      0      ,       0      ,       1    ]
[User C,     0     ,      0      ,      1      ,       0      ,       0    ]

The idea is to get users and dates dynamically. Any suggestions relevant to cross table mysql query would be really appreciated.

You are looking for a pivot query, but is the number of days (5 in the contrived example above) known and fixed? — Tim Biegeleisen, Mar 14 '16 at 08:11
@GoudaElalfy can you suggest something about achieving similar results? — Ayub, Mar 14 '16 at 08:13
@TimBiegeleisen The dates are dynamic, we cannot hard-code them, report is necessarily for each day — Ayub, Mar 14 '16 at 08:14
Then I think you will need some dynamic SQL to solve this problem. — Tim Biegeleisen, Mar 14 '16 at 08:15
Consider handling issues of data display in a presentation layer (e.g. a bit of PHP, if that's available) — Strawberry, Mar 14 '16 at 08:15
http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — Gouda Elalfy, Mar 14 '16 at 08:16
@GoudaElalfy link seems promising, but can you explain it a bit in this context? — Ayub, Mar 14 '16 at 08:29

Pivot / Cross table report of dates and user entries

0 Answers0