MySQL的透视表日期（垂直到水平的数据）(mysql pivot table date (vert

我一直在寻找小时，没有一个像样的答案。

我想要改造这个表：


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

至：


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

为了得到这样的结果，你会想透视数据。 MySQL不具有旋转功能，但你可以使用聚合函数与CASE表达式。

如果日期的数量是已知的，那么你可以硬编码查询：

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

请参阅SQL拨弄演示

我实现了用户变量行号分配到内每个记录client_id组。

如果你有数目不详的日期，那么你将需要使用一份声明中动态创建的SQL：

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;

请参阅SQL拨弄演示。

他们都给出了结果：

| 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) |