In SQL, I need to turn my data horizontal from vertical

Question

I have simple tables of data where event times are assoictaed to an id. Each id can have 0, 1 or more event times.

So my data looks like this

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

I would like to a turn my data side ways and have the following

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

I've looked at crosstab, but I think it relates to fixed lists, whereas mine differs depending upon how many, if any observations there were.

Might be Mysql56 and I use a Heidi front end.

So looking at the replies (thank you) I guess I need to use PIVOT.

Any assistance in the syntax would be greatly appreciated. All in there about 800 thousand eventIds, and up to around a max of 20 eventtimes per id. The events should ideally be ordered by time, ie lowest to highest.

Thanks

Answer 1

You could solve it using a function or stored procedure, to build up the entire query dynamically.

The sqlfiddle

I show you a how by using the function buildQuery, see below!

create table events and data

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

create function 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//

execute the function

set @q = buildQuery();

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

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

run results

SqlFiddle

The sqlfiddle