-1

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.

Improve this question
  • 1
    Why have you marked this question as sql-sever, firebird, and interbase? – Anthony Genovese Dec 5 '19 at 22:23
  • Please clarify what database this is being written for, as the answer will vary depending on the actual Database. Include version as well (since different versions of databases have different capabilities.) – Laughing Vergil Dec 5 '19 at 22:39
  • Rather than close this question as unclear, I have pointed it at the canonical example Q & A. I also moved the rather long and specific title into the body. Please edit your question as others have requested. If the answer below and the canonical Q & A do not address your issue, please update your post to clarify. Thanks. – Paul White♦ Dec 5 '19 at 23:09
  • So this is called PIVOTING in SQL terminology, at least I know where I am floating now. – Tomay Dec 6 '19 at 14:30
  • @AnthonyGenovese Actually it was intended for Firebird v2.5, but when I didn't see a variety of tags in this dba section, I added interbase and sql-server. But it is late now because someone posted a reply assuming sql-server. – Tomay Dec 6 '19 at 14:34
0

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

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.