Rotating data in this fashion is called a pivot. Unfortunately MySQL does not have a pivot function so you will have to replicate it using an aggregate function and a CASE expression.
Sample Data: used for this query
CREATE TABLE Table1
(`user_id` int, `date` datetime, `value` int)
;
INSERT INTO Table1
(`user_id`, `date`, `value`)
VALUES
(1, '2013-01-01 00:00:00', 100),
(2, '2013-01-01 00:00:00', 200),
(1, '2013-01-02 00:00:00', 500)
;
CREATE TABLE Table2
(`user_id` int, `user_names` varchar(4))
;
INSERT INTO Table2
(`user_id`, `user_names`)
VALUES
(1, 'John'),
(2, 'Tim')
;
If you know all of the values to turn into columns (for your example the names), then you could hard-code them and the SQL will be similar to this:
select
date,
max(case when rownum = 1 then value end) as John,
max(case when rownum = 2 then value end) as Tim
from
(
select date, value, user_names,
@row:=case when @prev=date then @row else 0 end + 1 as rownum,
@prev:=date
from
(
select t1.date, t1.value, t2.user_names
from table1 t1
inner join table2 t2
on t1.user_id = t2.user_id
order by date, user_names
) d, (SELECT @row:=0, @prev:=null) r
order by date, user_names
) src
group by date
See SQL Fiddle with Demo. As you can see, I had to implement user variables to assign a row number to each name within the date. This tells you how many distinct name values there will be to turn into columns.
For your situation though, you have an unknown number of names for each date, so you will need to use dynamic SQL within a prepared statement.
In this case the code will be similar to this:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when rownum = ',
rownum,
' then value end) AS `',
user_names, '`'
)
) INTO @sql
from
(
select date, value, user_names,
@row:=case when @prev=date then @row else 0 end + 1 as rownum,
@prev:=date
from
(
select t1.date, t1.value, t2.user_names
from table1 t1
inner join table2 t2
on t1.user_id = t2.user_id
order by date, user_names
) d, (SELECT @row:=0, @prev:=null) r
order by date, user_names
) src;
SET @sql = CONCAT('SELECT date, ', @sql, '
from
(
select date, value, user_names,
@row:=case when @prev=date then @row else 0 end + 1 as rownum,
@prev:=date
from
(
select t1.date, t1.value, t2.user_names
from table1 t1
inner join table2 t2
on t1.user_id = t2.user_id
order by date, user_names
) d, (SELECT @row:=0, @prev:=null) r
order by date, user_names
) src
group by date');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo. Both versions will give the result:
| DATE | JOHN | TIM |
--------------------------------------------------
| January, 01 2013 00:00:00+0000 | 100 | 200 |
| January, 02 2013 00:00:00+0000 | 500 | (null) |
