Separating fields in the same column

Question

I need to separate items in a column to show in the same row but having trouble coming up with the query to get this done

For example, here is how the table shows below currently:

person_id	person_name	test_date
000001	person1	11/12/2024
000001	person1	12/18/2024
000001	person1	01/12/2025
000002	person2	10/01/2024
000002	person2	11/01/2024
000002	person2	12/01/2024
000002	person2	01/01/2025

Would want to form it something like below:

person_id	person_name	test_date1	test_date2	test_date3	test_date4
000001	person1	11/12/2024	12/18/2024	01/12/2025
000002	person2	10/01/2024	11/01/2024	12/01/2024	01/01/2025

Thank you!

Answer 1

We can handle this using pivoting logic with the help of ROW_NUMBER():

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY test_date) rn
    FROM yourTable t
)

SELECT
    person_id,
    person_name,
    MAX(CASE WHEN rn = 1 THEN test_date END) AS test_date1,
    MAX(CASE WHEN rn = 2 THEN test_date END) AS test_date2,
    MAX(CASE WHEN rn = 3 THEN test_date END) AS test_date3,
    MAX(CASE WHEN rn = 4 THEN test_date END) AS test_date4
FROM cte
GROUP BY
    person_id,
    person_name
ORDER BY
    person_id;