How to Pivot Row Data into Columns Using Oracle SQL's PIVOT Operator?

Oracle SQL Pivot Query

The goal of this query is to convert row-organized data into columns, where each column represents a specific value in the input row.

Consider the following form:

月份	数值
1	100
2	200
3	300
4	400
5	500
6	600

Our goal is to pivot the data so that the output table looks like this:

一月	二月	三月	四月	五月	六月
100	200	300	400	500	600

Oracle 11g and above

Starting with Oracle 11g, the PIVOT operator provides a straightforward way to do this:

<code class="language-sql">CREATE TABLE tq84_pivot (
  month NUMBER,
  value NUMBER
);

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

-- 插入额外的行以进行演示
INSERT INTO tq84_pivot VALUES(1, 400);
INSERT INTO tq84_pivot VALUES(2, 350);
INSERT INTO tq84_pivot VALUES(4, 150);

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>

This query uses the PIVOT operator with a 'FOR' clause to specify the pivot column and associated values. By grouping the data by 'month' column and aggregating 'value' column using SUM(), we get the desired output where each month has its corresponding value.

The above is the detailed content of How to Pivot Row Data into Columns Using Oracle SQL's PIVOT Operator?. For more information, please follow other related articles on the PHP Chinese website!