如何使用带双列的SQL PIVOT转换SQL Developer查询结果？

Got it, let's walk through how to use Oracle's PIVOT function to transform your row-based charge data into a clean columnar format. Here's a tailored solution for your scenario:

Step 1: Prepare the Base Data (with Cleanup) #

First, we'll wrap your original query in a CTE (Common Table Expression) to simplify the pivot logic. We'll also fix the decimal separator in CHARGE_VALUE—Oracle uses . as the default decimal marker, and your data uses ,, so we need to convert that to a valid numeric type:

WITH charge_data AS (
    SELECT 
        A.order_number,
        A.TOP_MODEL_LINE_ID,
        C.CHARGE_NAME,
        -- Convert comma-separated decimal to Oracle's numeric format
        TO_NUMBER(REPLACE(C.CHARGE_VALUE, ',', '.')) AS CHARGE_VALUE
    FROM TABLE1 A
    JOIN TABLE2 B ON C.list_line_id = B.list_line_id
    JOIN TABLE3 C ON C.line_id = A.TOP_MODEL_LINE_ID
    WHERE A.order_number = '4411001286'
)

I switched to explicit JOIN syntax instead of comma-separated tables—it's more readable and aligns with modern SQL best practices!

Step 2: Apply the PIVOT Function #

Now we'll use PIVOT to turn each unique CHARGE_NAME into a column, with CHARGE_VALUE as the corresponding cell value. Since each order_number + TOP_MODEL_LINE_ID pair has exactly one entry per charge name, we can use MAX() (or SUM(), MIN()—all will work here) as the aggregation function:

SELECT *
FROM charge_data
PIVOT (
    MAX(CHARGE_VALUE)
    FOR CHARGE_NAME IN (
        'H Ar' AS H_Ar,
        'TC Tot' AS TC_Tot,
        'MN PreV' AS MN_PreV,
        'LO Offloading' AS LO_Offloading,
        'Costi trasporto' AS Costi_Trasporto
    )
);

What This Does: #

• The PIVOT clause automatically groups the data by order_number and TOP_MODEL_LINE_ID (the columns not referenced in the pivot logic).
• Each value in CHARGE_NAME becomes a new column—we use aliases like H_Ar to avoid spaces in column names, which prevents syntax issues.
• The MAX(CHARGE_VALUE) pulls the exact value for each charge name in the group (since there's only one value per group, any aggregation function that returns a single value works).

Example Output #

Your result will be a single row with all charges as columns, like this:

Key Notes: #

• Static vs. Dynamic Pivot: This is a static pivot—you have to list all possible CHARGE_NAME values in the IN clause. If you need to handle dynamic charge names (unknown at query time), you'd need to use PL/SQL to generate the pivot query dynamically, but that's more complex.
• Data Type Handling: The TO_NUMBER(REPLACE(...)) step is only necessary if CHARGE_VALUE is stored as a string with commas. If it's already a numeric type, you can skip this conversion.
• Aggregation Choice: Since each charge name appears exactly once per group, any aggregation function that returns the single value works—MAX, MIN, SUM, or even AVG.

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