How to Dynamically Pivot Oracle SQL Tables with Dynamic Values?

Oracle SQL dynamic pivot and dynamic value

A common challenge when using pivot tables in Oracle SQL is creating pivot tables with dynamic values. The traditional approach is to manually add new values ​​to the static string used in the pivot table IN statement, which is inefficient.

Limitations of dynamic IN statements

Embedding dynamic statements directly into PIVOT IN statements using subqueries, PL/SQL variables or nested queries is not supported. Additionally, using PIVOT XML may result in suboptimal output.

Solution: Create a dynamic IN string

To overcome these limitations, a method of creating dynamic IN strings can be used. Here's a step-by-step guide:

1. Create a SELECT statement for generating dynamic strings:

   • Use COLUMN NEW_VALUE and LISTAGG to concatenate the desired values ​​into a single string, separated by commas.
   • Store this string in a variable, such as str_in_statement.

2. Example: Consider the following example table:

<code>| myNumber | myValue | myLetter |
|---|---|---|
| 1 | 2 | A |
| 1 | 4 | B |
| 2 | 6 | C |
| 2 | 8 | A |
| 2 | 10 | B |
| 3 | 12 | C |
| 3 | 14 | A |</code>

To generate a dynamic IN string, execute the following statement:

<code class="language-sql">COLUMN temp_in_statement new_value str_in_statement
SELECT DISTINCT 
    LISTAGG('''' || myLetter || ''' AS ' || myLetter,',')
        WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement 
    FROM (SELECT DISTINCT myLetter FROM myTable);</code>

3. Incorporate dynamic strings into pivot queries:

   • Use a variable containing a dynamic string as the IN parameter of the pivot query.

4. Example pivot query:

<code class="language-sql">SELECT * FROM 
    (SELECT myNumber, myLetter, myValue FROM myTable)
    PIVOT (Sum(myValue) AS val FOR myLetter IN (&str_in_statement));</code>

Output:

<code>| MYNUMBER | A_VAL | B_VAL | C_VAL |
|---|---|---|---|
| 1 | 2 | 4 | NULL |
| 2 | 8 | 10 | 6 |
| 3 | 14 | NULL | 12 |</code>

Limitations:

A limitation of this method is that the maximum string size that can be concatenated is 4000 bytes.

The above is the detailed content of How to Dynamically Pivot Oracle SQL Tables with Dynamic Values?. For more information, please follow other related articles on the PHP Chinese website!