Oracle中如何实现行转列？表结构转换需求咨询

Hey there! Let's tackle this row-to-column pivot requirement for your table1 in Oracle. First, let's recap your data and desired output to make sure we're on the same page.

Original Table Structure & Data #

Your table1 has three fields: number, date, and time, with the following data:

Desired Output (Filtered for number='001') #

You want to pivot the time values into separate columns (time1 to time4) grouped by date:

Solution 1: Use Oracle's PIVOT Clause #

Oracle's PIVOT is a clean way to handle this. First, we'll assign a sequential number to each time entry for number='001' (grouped by date), then pivot those numbers into columns.

WITH numbered_times AS (
    SELECT 
        "date",
        "time",
        -- Assign a sequential number to each time per date/number, ordered by time
        ROW_NUMBER() OVER (PARTITION BY "date", "number" ORDER BY "time") AS time_seq
    FROM table1
    WHERE "number" = '001'
)
SELECT 
    "date",
    "1" AS time1,
    "2" AS time2,
    "3" AS time3,
    "4" AS time4
FROM numbered_times
PIVOT (
    MAX("time")  -- Aggregate function (we use MAX since each seq has one value)
    FOR time_seq IN (1, 2, 3, 4)  -- Define which sequence numbers to pivot into columns
);

How This Works: #

1. The CTE numbered_times adds a time_seq column, numbering each time entry in order for the same date and number.
2. The PIVOT clause takes those sequence numbers (1-4) and turns them into columns, using MAX() to grab the corresponding time value (since each sequence number maps to exactly one time here).

Solution 2: Conditional Aggregation (For Older Oracle Versions) #

If you're working with an Oracle version before 11g (where PIVOT wasn't introduced), you can use conditional aggregation to achieve the same result:

SELECT 
    "date",
    MAX(CASE WHEN time_seq = 1 THEN "time" END) AS time1,
    MAX(CASE WHEN time_seq = 2 THEN "time" END) AS time2,
    MAX(CASE WHEN time_seq = 3 THEN "time" END) AS time3,
    MAX(CASE WHEN time_seq = 4 THEN "time" END) AS time4
FROM (
    SELECT 
        "date",
        "time",
        ROW_NUMBER() OVER (PARTITION BY "date", "number" ORDER BY "time") AS time_seq
    FROM table1
    WHERE "number" = '001'
) t
GROUP BY "date";

How This Works: #

1. The inner subquery generates the same time_seq as the first solution.
2. The outer query uses CASE statements to pick out the time for each sequence number, then MAX() to aggregate (to ensure we get a single value per date/column), and groups by date.

Notes: #

• If number='001' has more than 4 time entries for a single date, these queries will only return the first 4 (ordered by time). If there are fewer than 4, the missing columns will show NULL.
• number and date are reserved keywords in Oracle, which is why we wrap them in double quotes. For better practice, consider renaming these fields to avoid conflicts (e.g., user_number, record_date).

内容的提问来源于stack exchange，提问作者mizkyd