Complex SQL query to one table

Question

There is a table:

event |id |timestamp
---------------------
event1|001|21-03-15
event2|001|22-03-15
event1|002|23-03-15
event2|002|24-03-15

What should be a request to display the result:

id |event1  |event2  |
----------------------
001|21-03-15|22-03-15|
002|23-03-15|24-03-15|

I think you first need to make a selection of unique id:

SELECT id FROM test GROUP BY id;

And then something like this:

SELECT timestamp
FROM   ... 
WHERE id IN (SELECT id FROM test GROUP BY id) AND event='event1';

Events are known in advance ('event1', 'event2'). If there are recurring events under one id, with different or the same timestamp, add columns to the result, for example:

id |event1  |event2  |event1  |event2  |
----------------------------------------
001|21-03-15|22-03-15|23-03-15|23-03-15|
002|23-03-15|24-03-15|NULL    |NULL    |

Can you expect that every ID will only have two events associated with it? And will they always be called "event1" and "event2"? — John Chrysostom, Dec 06 '15 at 12:35
imo, a useful resource for MySQL queries: [Common MySQL Queries - Extending Chapter 9 of Get it Done with MySQL 5&6](http://www.artfulsoftware.com/infotree/queries.php). Have a look at `pivot tables`. — Ryan Vincent, Dec 06 '15 at 12:56
1. every ID will have not only two events associated with, it willl have unexpected count of events 2. yes, events will always be called 'event1' and 'event2' — venom1_, Dec 06 '15 at 16:54

score 1 · Answer 1 · answered Dec 06 '15 at 12:48

1

You are looking for a simple "pivot" or "crosstab" trick:

SELECT id
     , min(CASE event WHEN 'event1' THEN timestamp END) AS event1
     , min(CASE event WHEN 'event2' THEN timestamp END) AS event2
FROM   test
GROUP  BY id
ORDER  BY id;

SQL Fiddle.

answered Dec 06 '15 at 12:48

Erwin Brandstetter

605,456
145
1,078
1,228

score 1 · Answer 2 · answered Dec 06 '15 at 16:59

I found a solution, query is looking like this:

SELECT A.id,GROUP_CONCAT(B.timestamp) AS event1, GROUP_CONCAT(C.timestamp) AS event2 FROM (select distinct id from test) A
   LEFT JOIN test B ON B.id=A.id and B.event='event1'
   LEFT JOIN test C ON C.id=A.id and C.event='event2' GROUP BY A.id

score 0 · Answer 3 · edited May 23 '17 at 12:08

You can join records that have event1 with records that have event2:

SELECT t1.id as id, t1.timestamp as event1, t2.timestamp as event2 
FROM test t1 LEFT JOIN test t2
    ON t1.id = t2.id AND t1.event = 'event1' and t2.event = 'event2'

The query assumes that you always have event1, so LEFT JOIN was used.

If you need to handle cases where only event2 is available, you can emulate FULL OUTER JOIN as described in Full Outer Join in MySQL

Complex SQL query to one table

3 Answers3