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

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, Dec 27 '13 at 14:21
Is there a **finite** number of events [that you want to display]? — gvee, Dec 27 '13 at 14:21
Sounds like to me that you are wanting to do a sort of pivot this link might help, but I don't know which dbms you are using http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx — MCP_infiltrator, Dec 27 '13 at 14:24
possible duplicate of [How to transform vertical data into horizontal data with SQL?](http://stackoverflow.com/questions/4071811/how-to-transform-vertical-data-into-horizontal-data-with-sql) — MrSimpleMind, Dec 27 '13 at 14:37
looking at the pivot examples i can see, they all relate to fixed data, whereas mine is sort of open ended. i'm no database guy as you have worked out. the guy that normally helps out me out with the simple stuff i need is away for a bit, so any help on PIVOT query would be greatly appreciated. thanks — John, Dec 28 '13 at 13:59

MrSimpleMind · Answer 1 · 2013-12-30T08:57:51.633

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

Events output

SqlFiddle

The sqlfiddle

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

1 Answers1