How Can I Pivot Flat Monthly Data in Oracle SQL for Easier Analysis?

Oracle SQL pivot table data reconstruction

Issue: Convert flat tabular data for pivot analysis

Suppose you have a table containing numerical data organized by month:

<code>MONTH | VALUE
-------|------
1      | 100
2      | 200
3      | 300
...</code>

The goal is to convert this data into a pivot format to more visually view the monthly values:

<code>MONTH_JAN | MONTH_FEB | MONTH_MAR | ...
---------|---------|---------|
100       | 200       | 300       | ...</code>

Oracle 11g and above: PIVOT operator

Oracle 11g and later introduces the PIVOT operator, which allows for easy pivoting of data. The syntax is as follows:

<code>SELECT *
FROM table_name
PIVOT (
  aggregate_function(column_name)
  FOR (pivot_column)
  IN (list_of_values)
);</code>

Example using PIVOT operator

Consider the following example data:

<code>CREATE TABLE tq84_pivot (
  month NUMBER,
  value NUMBER
);

INSERT INTO tq84_pivot VALUES (1, 100), (2, 200), (3, 300), (4, 400), (5, 500), (6, 600);

SELECT 
  *
FROM 
  tq84_pivot
PIVOT (
  SUM(value) AS sum_value
  FOR (month) IN (1 AS month_jan,
                  2 AS month_feb,
                  3 AS month_mar,
                  4 AS month_apr,
                  5 AS month_mai,
                  6 AS month_jun,
                  7 AS month_jul,
                  8 AS month_aug,
                  9 AS month_sep,
                 10 AS month_oct,
                 11 AS month_nov,
                 12 AS month_dec)
);</code>

The result is as expected:

<code>MONTH_JAN | MONTH_FEB | MONTH_MAR | ...
---------|---------|---------|
100       | 200       | 300       | ...</code>

The above is the detailed content of How Can I Pivot Flat Monthly Data in Oracle SQL for Easier Analysis?. For more information, please follow other related articles on the PHP Chinese website!