mysql - 在 SQL 中，我需要将数据从垂直方向变为水平方向

Question

我有简单的数据表，其中事件时间与 id 相关联。每个 id 可以有 0 个、1 个或多个事件时间。

所以我的数据是这样的

ID EventTime
----------
1  15
3  49
3  78
5  68
6  62
7  85
7  86

我想把我的数据放在一边，并有以下内容

ID Event1 Event2 Event3 Event4 etc
----------------------------------------
1   15
3   49       78
5   68
6   62
7   85       86

我查看过交叉表，但我认为它与固定列表有关，而我的则不同，具体取决于观察的数量(如果有的话)。

可能是 Mysql56，我使用的是 Heidi 前端。

所以看着回复(谢谢)我想我需要使用 PIVOT。

语法方面的任何帮助将不胜感激。总共有大约 80 万个 eventId，每个 ID 最多大约 20 个事件时间。理想情况下，事件应按时间排序，即从最低到最高。

谢谢

Answer 1

您可以使用函数或存储过程来解决它，以动态构建整个查询。

The sqlfiddle

我向您展示了如何使用函数 buildQuery，见下文!

创建表事件和数据

create table events (id int, eventtime int);
insert into events values (1, 15);
insert into events values (3, 49);
insert into events values (3, 78);
insert into events values (5, 68);
insert into events values (6, 62);
insert into events values (7, 85);
insert into events values (7, 86);

创建函数 buildQuery()

create function buildQuery() returns varchar(4000) 
not deterministic 
reads sql data 
begin 
  -- variables
  declare query varchar(4000);
  declare maxcols int;
  declare counter int;

  -- initialize
  set query   = '';
  set maxcols = 0;
  set counter = 0;

  -- get the max amount of columns
  select count(id) as maxevents into maxcols 
  from events 
  group by id 
  order by maxevents desc limit 1;

  -- build the query
  while counter < maxcols do
    set counter = counter + 1;
    set query=concat(query,',replace(substring(substring_index(group_concat(eventtime), '','',', counter,'),length(substring_index(group_concat(eventtime),'','',', counter,'-1)) + 1),'','','''') as event' ,counter);
  end while;

  -- return
  return query;
end//

执行函数

set @q = buildQuery();

set @q = concat('select id ', @q, '
                 from events 
                 group by id');

prepare stmnt from @q;
execute stmnt;
deallocate prepare stmnt;

运行结果

SQLFiddle

The sqlfiddle