sql - Separating fields in the same column - Stack Overflow

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

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!

• sql
• case

Share Improve this question Follow edited Jan 30 at 8:22 jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked Jan 30 at 0:18 agloriaagloria 1333 bronze badges 6

• 2 This isn't something SQL is meant for: SQL is all about sets of fixed-tuples not sets of variadic-tuples. Instead, this is best done by your presentation layer (i.e. your reporting engine, or in Excel, or PHP, etc). – Dai Commented Jan 30 at 0:23
• 2 This has been asked and answered here many times before. See stackoverflow.com/search?q=sql+rows+to+columns. I can't decide on the appropriate duplicate to use for closure because you failed to provide a tag for your RDBMS (which you should always do when asking a SQL question). – Ken White Commented Jan 30 at 0:28
• 2 This question is similar to: Convert Rows to Columns SQL. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – Ken White Commented Jan 30 at 0:31
• 1 Please tag your RDBMS - all SQL dialects are different. – Dale K Commented Jan 30 at 3:17
• 1 What's the expected result if someone later inserts another person2 row? Do you suddenly want a test_date5 column? – jarlh Commented Jan 30 at 8:21

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

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;