How to Pivot Data from Rows to Columns in Oracle SQL?

Creating pivot queries in Oracle SQL

In Oracle SQL, pivot queries are used to convert the vertical orientation of data into horizontal orientation. A common scenario is to convert row-organized data into a column-presented format.

Example

Consider a data table containing columns "MONTH" and "VALUE":

MONTH	VALUE
1	100
2	200
3	300
4	400
5	500
6	600

Expected results

The expected result is a pivot table with a column for each unique month showing the corresponding "VALUE":

MONTH_JAN	MONTH_FEB	MONTH_MAR	MONTH_APR	MONTH_MAY	MONTH_JUN
100	200	300	400	500	600

Oracle 11g and above

In Oracle 11g and later, the PIVOT operator can be used:

<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 will create the required pivot table.

The above is the detailed content of How to Pivot Data from Rows to Columns in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!