sql - mysql pivot table date (vertical to horizontal data)

Question

I have been searching for hours without a decent answer.

I want to transform this table:

Client_id    Date
-----------  ------------  
1            2013-02-03    
1            2013-02-10
1            2013-05-12
2            2013-02-03
2            2013-07-15

To:

Client_id    Date1          Date2         Date3         Date4, Date5, Date6...
-----------  ------------   ------------  ------------  ------------
1            2013-02-03     2013-02-10    2013-05-12
2            2013-02-03     2013-07-15

Answer 1

In order to get this result, you will want to pivot the data. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression.

If the number of dates is known, then you can hard-code the query:

select client_id,
  max(case when rownum = 1 then date end) Date1,
  max(case when rownum = 2 then date end) Date2,
  max(case when rownum = 3 then date end) Date3
from
(
  select client_id,
    date,
    @row:=if(@prev=client_id, @row,0) + 1 as rownum,
    @prev:=client_id 
  from yourtable, (SELECT @row:=0, @prev:=null) r
  order by client_id, date
) s
group by client_id
order by client_id, date

See SQL Fiddle with Demo

I implemented user variables to assign a row number to each record within the client_id group.

If you have an unknown number of dates, then you will need to use a prepared statement to create the sql dynamically:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN rownum = ',
      rownum,
      ' THEN date END) AS Date_',
      rownum
    )
  ) INTO @sql
from
(
  select client_id,
    date,
    @row:=if(@prev=client_id, @row,0) + 1 as rownum,
    @prev:=client_id 
  from yourtable, (SELECT @row:=0) r
  order by client_id, date
) s
order by client_id, date;


SET @sql 
  = CONCAT('SELECT client_id, ', @sql, ' 
           from
           (
             select client_id,
               date,
               @row:=if(@prev=client_id, @row,0) + 1 as rownum,
               @prev:=client_id 
             from yourtable, (SELECT @row:=0) r
             order by client_id, date
           ) s
           group by client_id
           order by client_id, date');

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

See SQL Fiddle with Demo.

They both give the result:

| CLIENT_ID |                          DATE_1 |                          DATE_2 |                     DATE_3 |
--------------------------------------------------------------------------------------------------------------
|         1 | February, 03 2013 00:00:00+0000 | February, 10 2013 00:00:00+0000 | May, 12 2013 00:00:00+0000 |
|         2 | February, 03 2013 00:00:00+0000 |     July, 15 2013 00:00:00+0000 |                     (null) |