SQL - How to place sequence of order of events for one person in one row?

Question

I have the following tables:

Customer Table

Customer_ID
1
2
3

Order Table

Customer_ID Order_ID    Brand   Order_Date
1   101 A   01/02/2010
1   102 B   05/02/2010
1   103 A   06/01/2014
2   104 B   02/01/2013
2   105 D   02/02/2013
3   106 A   07/03/2013
3   107 B   07/04/2013
3   108 C   07/05/2013
3   109 D   07/07/2013
3   110 E   07/11/2013

How do I sort of transpose this data from two separate tables to get the one results window:

Customer    Order1  Order2  Order3  Order4  Order5
1   101 102 103
2   104 105
3   106 107 108 109 110

Customer    Order1  Order2  Order3  Order4  Order5
1   101 102 103     
2   104 105         
3   106 107 108 109 110

Customer    Order1  Order2  Order3  Order4  Order5
1   A   B   A       
2   B   D           
3   A   B   C   D   E

The Order1-n column would only go up as high as the most number of orders a single customer has so if Customer 4 has 10 orders, the column would be Order1 Order2 Order3 ... Order10

Thanks,

You should be able to find your answers here: http://stackoverflow.com/questions/7674786/mysql-pivot-table — Mike, Oct 08 '14 at 13:33
MySQL? Microsoft SQL Server? Oracle? Postgres? DB2? what database vendor and version do you use? — MrSimpleMind, Mar 18 '15 at 09:16

score 0 · Answer 1 · edited May 23 '17 at 11:50

You didn't mention what kind of database you are using. In general you need to use pivot(as for mysql) or crosstab (for PostgreSQL). You can surely find related questions on SO. Mike mentiond the topic about MySQL's pivot, while this one explains the PostgreSQL's crosstab: Group By and add columns

So in PostgreSQL this would be:

SELECT * FROM crosstab(
    'SELECT "Customer_ID", null /*doesnt really matter*/, "Order_ID"  FROM  orders'
) AS (Customer_ID int
    , Order1 int
    , order2 int
    , order3 int
    , order4 int
    , order5 int
    , order6 int
)
;
 customer_id | order1 | order2 | order3 | order4 | order5 | order6
-------------+--------+--------+--------+--------+--------+--------
           1 |    101 |    102 |    103 |        |        |
           2 |    104 |    105 |        |        |        |
           3 |    106 |    107 |    108 |    109 |    110 |
(3 wiersze)


SELECT * FROM crosstab(
    'SELECT "Customer_ID", null /*doesnt really matter*/, "Brand"  FROM  orders'
) AS (Customer_ID int
    , "Brand_1" VARCHAR
    , "Brand_2" VARCHAR
    , "Brand_3" VARCHAR
    , "Brand_4" VARCHAR
    , "Brand_5" VARCHAR
    , "Brand_6" VARCHAR
)
;
 customer_id | Brand_1 | Brand_2 | Brand_3 | Brand_4 | Brand_5 | Brand_6
-------------+---------+---------+---------+---------+---------+---------
           1 | A       | B       | A       |         |         |
           2 | B       | D       |         |         |         |
           3 | A       | B       | C       | D       | E       |
(3 wiersze)

If this is your DBMS - please refer to the doc: http://www.postgresql.org/docs/9.1/static/tablefunc.html#AEN142967 As crosstab is part of an additional module (tableFunc) - you need to "turn it on", because it is not enabled by default (probably this is the cause, why it doesn't work on sqlFiddle)

score 0 · Answer 2 · answered Mar 18 '15 at 09:24

I have done this before by using a stored procedure for building my query, which will allow you have lots of order_id columns. I assume you are using MySQL.

This shows how to output the order_id, you could do the same for output the brand too.

The sqlfiddle

create table events and data

create table Customer (customer_id int);
create table Orders (customer_id int, order_id int, brand char, order_date date);
insert into customer values(1);
insert into customer values(2);
insert into customer values(3);
insert into orders values(1, 101,'A', '2010-02-01');
insert into orders values(1, 102,'B', '2010-02-05');
insert into orders values(1, 103,'A', '2014-01-06');

insert into orders values(2, 204,'B', '2013-01-02');
insert into orders values(2, 205,'D', '2013-02-02');

insert into orders values(3, 306,'A', '2013-03-07');
insert into orders values(3, 307,'B', '2013-04-07');
insert into orders values(3, 308,'C', '2013-05-07');
insert into orders values(3, 309,'D', '2013-07-07');
insert into orders values(3, 310,'E', '2013-11-07');
insert into orders values(3, 311,'F', '2013-11-07');

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(distinct order_id) as maxorders into maxcols 
  from Orders 
  group by customer_id 
  order by maxorders desc limit 1;

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

  -- return
  return query;
end//

execute the function

set @q = buildQuery();

set @q = concat('select customer_id ', @q, '
                 from Orders 
                 group by customer_id');

prepare s from @q;
execute s;
deallocate prepare s;

run results

The results

SqlFiddle

The sqlfiddle

SQL - How to place sequence of order of events for one person in one row?

2 Answers2