Oracle SQL Pivot Query

Oracle SQL pivot query

Oracle 9i+ supports:

SELECT SUM(CASE WHEN t.month = 1 THEN t.value ELSE 0 END) AS JAN,
       SUM(CASE WHEN t.month = 2 THEN t.value ELSE 0 END) AS FEB,
       SUM(CASE WHEN t.month = 3 THEN t.value ELSE 0 END) AS MAR,
       SUM(CASE WHEN t.month = 4 THEN t.value ELSE 0 END) AS APR,
       SUM(CASE WHEN t.month = 5 THEN t.value ELSE 0 END) AS MAY,
       SUM(CASE WHEN t.month = 6 THEN t.value ELSE 0 END) AS JUN
  FROM YOUR_TABLE t

You only list two columns -- something like this should probably be grouped by year.

There is ANSI PIVOT (and UNPIVOT) syntax, but Oracle didn't support it until 11g. Prior to 9i, you'd have to replace the CASE statements with Oracle specific DECODE.

Pivot in Oracle based on multiple columns

You can definitely use multiple columns within a PIVOT clause, here is an example using your table setup:

SELECT *
FROM demo
PIVOT (MAX(VALUE) FOR (identifier_1, identifier_2) IN ((3000, 23) AS A3000_23, (3000, 24) AS A3000_24,
                                                        (3001, 25) AS A3001_25, (3001, 26) AS A3001_26));

N.B.: Excuse the "A" in the column names, you need to start the identifier with a character, not a number.

Here is a DBFiddle showing the results (LINK)

Obviously you can see how this would quickly grow out-of-hand if you need to list large amounts of PIVOT columns.

Oracle - PL/SQL Developer shows column name in a PIVOT query

You can give the columns an alias:

SELECT *
FROM   (SELECT 'your string' AS name FROM DUAL)
PIVOT (
  COUNT(1)
  FOR name IN (
    'your string' AS alias1,
    'other string' AS alias2
  )
)

or, if you want to use a non-ASCII alias then use quoted identifiers:

SELECT *
FROM   (SELECT 'your string' AS name FROM DUAL)
PIVOT (
  COUNT(1)
  FOR name IN (
    'your string' AS "non-ASCII alias1",
    'other string' AS "non-ASCII alias2"
  )
)

Create Pivot view in SQL from a SQL table

A stored function(or procedure) might be created in order to create a SQL for Dynamic Pivoting, and the result set is loaded into a variable of type SYS_REFCURSOR :

CREATE OR REPLACE FUNCTION Get_Categories_RS RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols_1    VARCHAR2(32767);
  v_cols_2    VARCHAR2(32767);  
BEGIN
  SELECT LISTAGG( ''''||"level"||''' AS "'||"level"||'"' , ',' )
          WITHIN GROUP ( ORDER BY "level" DESC )
    INTO v_cols_1
    FROM (
          SELECT DISTINCT "level"
            FROM temp
          );

  SELECT LISTAGG( 'MAX(CASE WHEN category = '''||category||''' THEN "'||"level"||'" END) AS "'||"level"||'_'||category||'"' , ',' )
          WITHIN GROUP ( ORDER BY category, "level" DESC )
    INTO v_cols_2
    FROM (
          SELECT DISTINCT "level", category
            FROM temp
          );

  v_sql :=
  'SELECT "set", '|| v_cols_2 ||'
     FROM
     (
      SELECT *
        FROM temp
       PIVOT
       (
        MAX(value) FOR "level" IN ( '|| v_cols_1 ||' )
       )
      )
      GROUP BY "set"
      ORDER BY "set"'; 

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;

in which I used two levels of pivoting : the first is within the inner query involving PIVOT Clause, and the second is in the outer query having the conditional aggregation logic. Notice that the order of levels should be in the descending order( Z, Y, X ) within the expected result as conforming to the description.

And then invoke

VAR rc REFCURSOR
EXEC :rc := Get_Categories_RS;
PRINT rc

from SQL Developer's Command Line in order to get the result set

Btw, avoid using reserved keywords such as set and level as in your case. I needed to quote them in order to be able to use.

Pivot query Oracle SQL

Here's one option:

SQL> select msgid,
  2    max(case when key = 'height' then colvalue end) height,
  3    max(case when key = 'length' then colvalue end) length,
  4    max(case when key = 'width'  then colvalue end) width
  5  from test
  6  group by msgid
  7  order by msgid;

     MSGID HEIGHT     LENGTH     WIDTH
---------- ---------- ---------- ----------
        15 18         19         20
        16 21         22         23
        17 24         25         26

SQL>

Or, with pivot:

SQL> select *
  2  from test
  3  pivot
  4    (max(COLVALUE)
  5     FOR KEY IN ('height','length','width')
  6    )
  7  order by 1;

     MSGID 'he 'le 'wi
---------- --- --- ---
        15 18  19  20
        16 21  22  23
        17 24  25  26

SQL>

---

Related Topics