How to join all different rows with the same ID but a different DATE
and return it as a unique row with new columns like DATE_0
, DATE_1
, DATE_2
, ...?
Consider this table:
+----+------------+
| ID | DATE |
+----+------------+
| 0 | 01/12/2007 |
| 0 | 15/02/2018 |
| 1 | 21/05/2006 |
| 1 | 03/12/2017 |
| 1 | 01/03/2007 |
| 1 | 23/11/2018 |
| 2 | 15/10/2019 |
| 3 | 11/10/2019 |
| 3 | 22/07/2000 |
| 4 | 08/05/2001 |
| 4 | 07/03/1998 |
+----+------------+
I need a SQL query so this table will become like this:
+----+------------+------------+------------+------------+
| ID | DATE_0 | DATE_1 | DATE_2 | DATE_3 |
+----+------------+------------+------------+------------+
| 0 | 01/12/2007 | 15/02/2018 | NULL | NULL |
| 1 | 21/05/2006 | 03/12/2017 | 01/03/2007 | 23/11/2018 |
| 2 | 15/10/2019 | NULL | NULL | NULL |
| 3 | 11/10/2019 | 22/07/2000 | NULL | NULL |
| 4 | 08/05/2001 | 07/03/1998 | NULL | NULL |
+----+------------+------------+------------+------------+
Keep in mind that I already know that the maximum number same ID rows wont exceed 4.
Thank you in advance.