i've face with a challenging case. I really appreciate if someone helps ... imagine we have following set of records:
| ID | Date |
|---|---|
| 20220614175302951087 | 6/15/2022 13:27 |
| 20220614175302951087 | 6/15/2022 14:06 |
| 20220614175302951088 | 6/15/2022 13:27 |
| 20220614175302951088 | 6/15/2022 14:07 |
| 20220614175302951088 | 6/15/2022 14:42 |
| 20220614175302951089 | 6/15/2022 13:28 |
| 20220614175302951089 | 6/15/2022 14:08 |
| 20220614175302951089 | 6/15/2022 14:39 |
| 20220614175302951090 | 6/15/2022 13:38 |
| 20220614175802951200 | 6/15/2022 9:58 |
| 20220614175802951200 | 6/15/2022 10:28 |
i need the result to be shown as
| ID | Date1 | Date2 | Date3 |
|---|---|---|---|
| 20220614175302951087 | 6/15/2022 13:27 | 6/15/2022 14:06 | |
| 20220614175302951088 | 6/15/2022 13:27 | 6/15/2022 14:07 | 6/15/2022 14:42 |
| 20220614175302951089 | 6/15/2022 13:28 | 6/15/2022 14:08 | 6/15/2022 14:39 |
| 20220614175802951200 | 6/15/2022 9:58 | 6/15/2022 10:28 |
thank you guys
CodePudding user response：
If the no of dates are maximum 3, You can use the conditional aggregation method along with ROW_NUMBER, to achieve your result -
SELECT ID,
MAX(CASE WHEN RN = 1 THEN DATE ELSE NULL END) Date1,
MAX(CASE WHEN RN = 2 THEN DATE ELSE NULL END) Date2,
MAX(CASE WHEN RN = 3 THEN DATE ELSE NULL END) Date3
FROM (SELECT ID, DATE,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE)
FROM YOUR_TABLE)
GROUP BY ID;