CODEWITHSUNDEEP
sqloracle-databasepivot
Yahoo-Me
Yahoo-Me

Reputation: 5033

Transposing a table through select query

I have a table like:

Key  type      value
---------------------
40     A        12.34    
41     A        10.24    
41     B        12.89

I want it in the format:

Types     40    41     42 (keys)
---------------------------------
A       12.34   10.24  XXX    
B       YYY     12.89  ZZZ

How can this be done through an SQL query. Case statements, decode??

Upvotes: 2

Views: 4039

Answers (4)

outis
outis

Reputation: 77400

What you're looking for is called a "pivot" (see also "Pivoting Operations" in the Oracle Database Data Warehousing Guide):

SELECT * 
  FROM tbl
  PIVOT(SUM(value) FOR Key IN (40, 41, 42))

It was added to Oracle in 11g. Note that you need to specify the result columns (the values from the unpivoted column that become the pivoted column names) in the pivot clause. Any columns not specified in the pivot are implicitly grouped by. If you have columns in the original table that you don't wish to group by, select from a view or subquery, rather than from the table.

You can engage in a bit of wizardry and get Oracle to create the statement for you, so that you don't need to figure out what column values to pivot on. In 11g, when you know the column values are numeric:

SELECT
    'SELECT * FROM tbl PIVOT(SUM(value) FOR Key IN ('
    || LISTAGG(Key, ',') WITHIN GROUP (ORDER BY Key)
    || ');'
  FROM tbl;

If the column values might not be numeric:

SELECT
    'SELECT * FROM tbl PIVOT(SUM(value) FOR Key IN (\''
    || LISTAGG(Key, '\',\'') WITHIN GROUP (ORDER BY Key)
    || '\'));'
  FROM tbl;

LISTAGG probably repeats duplicates (would someone test this?), in which case you'd need:

SELECT
    'SELECT * FROM tbl PIVOT(SUM(value) FOR Key IN (\''
    || LISTAGG(Key, '\',\'') WITHIN GROUP (ORDER BY Key)
    || '\'));'
  FROM (SELECT DISTINCT Key FROM tbl);

You could go further, defining a function that takes a table name, aggregate expression and pivot column name that returns a pivot statement by first producing then evaluating the above statement. You could then define a procedure that takes the same arguments and produces the pivoted result. I don't have access to Oracle 11g to test it, but I believe it would look something like:

CREATE PACKAGE dynamic_pivot AS
  -- creates a PIVOT statement dynamically
  FUNCTION pivot_stmt (tbl_name IN varchar2(30),
                       pivot_col IN varchar2(30), 
                       aggr IN varchar2(40), 
                       quote_values IN BOOLEAN DEFAULT TRUE)
      RETURN varchar2(300);
  PRAGMA RESTRICT_REFERENCES (pivot_stmt, WNDS, RNPS);

  -- creates & executes a PIVOT
  PROCEDURE pivot_table (tbl_name IN varchar2(30),
                         pivot_col IN varchar2(30), 
                         aggr IN varchar2(40), 
                         quote_values IN BOOLEAN DEFAULT TRUE);
END dynamic_pivot;

CREATE PACKAGE BODY dynamic_pivot AS
  FUNCTION pivot_stmt (
      tbl_name IN varchar2(30), 
      pivot_col IN varchar2(30), 
      aggr_expr IN varchar2(40), 
      quote_values IN BOOLEAN DEFAULT TRUE
    ) RETURN varchar2(300)
  IS
     stmt VARCHAR2(400);
     quote VARCHAR2(2) DEFAULT '';
  BEGIN
     IF quote_values THEN
       quote :=  '\\\'';
     END IF;
     -- "\||" shows that you are still in the dynamic statement string
     -- The input fields aren't sanitized, so this is vulnerable to injection
     EXECUTE IMMEDIATE 'SELECT \'SELECT * FROM ' || tbl_name 
             || ' PIVOT(' || aggr_expr || ' FOR ' || pivot_col 
             || ' IN (' || quote || '\' \|| LISTAGG(' || pivot_col
                        || ', \'' || quote || ',' || quote
             || '\') WITHIN GROUP (ORDER BY ' || pivot_col || ') \|| \'' || quote 
             || '));\' FROM (SELECT DISTINCT ' || pivot_col || ' FROM ' || tbl_name || ');'
       INTO stmt;
     RETURN stmt;
  END pivot_stmt;

  PROCEDURE pivot_table (tbl_name IN varchar2(30), pivot_col IN varchar2(30), aggr_expr IN varchar2(40), quote_values IN BOOLEAN DEFAULT TRUE) IS
  BEGIN
    EXECUTE IMMEDIATE pivot_stmt(tbl_name, pivot_col, aggr_expr, quote_values);
  END pivot_table;
END dynamic_pivot;

Note: the length of the tbl_name, pivot_col and aggr_expr parameters comes from the maximum table and column name length. Note also that the function is vulnerable to SQL injection.

In pre-11g, you can apply MySQL pivot statement generation techniques (which produces the type of query others have posted, based on explicitly defining a separate column for each pivot value).

Upvotes: 3

Harrison
Harrison

Reputation: 9090

If you do not have access to 11g, you can utilize a string aggregation and a grouping method to approx. what you are looking for such as

with data as(
SELECT 40 KEY , 'A' TYPE , 12.34 VALUE FROM DUAL UNION 
SELECT 41 KEY , 'A' TYPE , 10.24 VALUE FROM DUAL UNION 
SELECT 41 KEY , 'B' TYPE , 12.89 VALUE FROM DUAL 
)
          select   
                 TYPE ,                  
                 wm_concat(KEY) KEY ,
                wm_concat(VALUE) VALUE 
              from   data       
        GROUP BY TYPE;  

type     KEY     VALUE
------ ------- -----------
A       40,41   12.34,10.24
B       41      12.89

This is based on wm_concat as shown here: http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

I'm going to leave this here just in case it helps, but I think PIVOT or MikeyByCrikey's answers would best suit your needs after re-looking at your sample results.

Upvotes: 0

Mike Meyers
Mike Meyers

Reputation: 2895

Pivot does simplify things greatly. Before 11g however, you need to do this manually.

select 
  type, 
  sum(case when key = 40 then value end) as val_40,
  sum(case when key = 41 then value end) as val_41,
  sum(case when key = 42 then value end) as val_42
from my_table
group by type;

Upvotes: 1

krusty.ar
krusty.ar

Reputation: 4060

Never tried it but it seems at least Oracle 11 has a PIVOT clause

Upvotes: 0

Related Questions