Sql-server – Pivoting date data

firebirdinterbasepivotsql server

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.

Best Answer

Assuming you have SQL SERVER as a base

You can try with this

DECLARE @HelpTable TABLE(
ID INT,
Fecha DATE
)

INSERT INTO @HelpTable VALUES (0,'2007/01/12'),
(0,'2018/02/15'),
(1,'2006/05/21')
,(1,'2017/12/03')
,(1,'2007/09/01')
,(1,'2018/11/23')



-- CTE TO GET THE VALUES 
    ;WITH CTE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS N, A.Fecha as part ,ID AS IDReal
    FROM @HelpTable A
    )
--SELECT THE DATA
    SELECT IDReal,[1],[2],[3],[4]--[5],[6] etc
FROM(
    SELECT IDReal,part,
          ROW_NUMBER() OVER(partition by IDReal ORDER BY IDReal) AS [rn]
    FROM CTE 
    ) AS [a] 
    --USE A PIVOT
    PIVOT(MAX(part) FOR [rn] IN([1],[2],[3],[4])) AS [pvt];

Do not fill in all the data but with that you can give yourself an idea I attach a link where you can see the example

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=73ef9d5cf770df291f4ed95d7a86d1a1