Oracle数据库SQL表透视（Pivot）操作技术求助

Hey there! Since you're new to SQL and need help with Oracle's PIVOT operation, let's walk through this using your sample data. I'll start with clear examples and explain each part so you can follow along easily.

First, let's assume your table is named SALE_PROMOTIONS. Here's how you can recreate it with your sample data (this helps test the pivot queries):

CREATE TABLE SALE_PROMOTIONS (
    Sale_Start VARCHAR2(5),
    Sale_End VARCHAR2(5),
    Store VARCHAR2(20),
    Promotion VARCHAR2(20)
);

INSERT INTO SALE_PROMOTIONS VALUES ('1/1', '4/1', 'Nike', '10% OFF');
INSERT INTO SALE_PROMOTIONS VALUES ('1/1', '4/1', 'Adidas', '20% OFF');
INSERT INTO SALE_PROMOTIONS VALUES ('1/1', '6/1', 'Reebok', '30% OFF');
INSERT INTO SALE_PROMOTIONS VALUES ('2/1', '4/1', 'Nike', '40% OFF');
INSERT INTO SALE_PROMOTIONS VALUES ('2/1', '4/1', 'Reebok', '50% OFF');
INSERT INTO SALE_PROMOTIONS VALUES ('3/1', '4/1', 'Adidas', '60% OFF');
INSERT INTO SALE_PROMOTIONS VALUES ('3/1', '4/1', 'Sketchers', '70% OFF');
COMMIT;

Static PIVOT Example (Known Store Values) #

If you know all the Store values upfront, a static pivot is straightforward—it turns each Store into a column, showing the promotion for each sale period.

Here's the query:

SELECT *
FROM SALE_PROMOTIONS
PIVOT (
    -- Use MAX (or MIN, since each (Sale_Start, Sale_End, Store) has one unique promotion)
    MAX(Promotion)
    -- Specify we're pivoting on the Store column
    FOR Store IN (
        'Nike' AS Nike,
        'Adidas' AS Adidas,
        'Reebok' AS Reebok,
        'Sketchers' AS Sketchers
    )
)
ORDER BY Sale_Start, Sale_End;

What this does:

• The PIVOT clause takes distinct values from the Store column and converts them into separate columns.
• We use MAX(Promotion) because Oracle requires an aggregate function in pivot logic—since each combination of sale dates and store has exactly one promotion, MAX/MIN will just return that single value.
• The IN clause lists each Store value we want as a column, with a clean alias (so column names don't have quotes).

The result will look like this:

Dynamic PIVOT (Unknown/Changing Store Values) #

If your Store list might grow or change over time, a static pivot isn't practical. You can use PL/SQL to generate a dynamic pivot query that adapts to new stores automatically:

DECLARE
    v_cols VARCHAR2(1000);
    v_sql VARCHAR2(2000);
BEGIN
    -- Get all distinct Store values and format them for the IN clause
    SELECT LISTAGG('''' || Store || ''' AS ' || Store, ', ') WITHIN GROUP (ORDER BY Store)
    INTO v_cols
    FROM (SELECT DISTINCT Store FROM SALE_PROMOTIONS);

    -- Build the full pivot query
    v_sql := 'SELECT * FROM SALE_PROMOTIONS PIVOT (MAX(Promotion) FOR Store IN (' || v_cols || ')) ORDER BY Sale_Start, Sale_End';

    -- Execute the dynamic query
    EXECUTE IMMEDIATE v_sql;
END;
/

Notes on dynamic pivot:

• LISTAGG aggregates all distinct Store values into the exact format needed for the IN clause.
• EXECUTE IMMEDIATE runs the dynamically generated SQL statement. If you need to return results to a client, you'd use a ref cursor instead of just executing it.

Key Takeaways for Oracle PIVOT #

• Always use an aggregate function (MAX/MIN work great when you're dealing with unique values per group).
• Static pivots are best when you know all the values you want to turn into columns upfront.
• Dynamic pivots are ideal for datasets where the pivot values (like Store names) might change over time.

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