编程知识 cdmana.com

Leetcode problem solving series 1179. Reformat department table (Oracle pivot row column conversion function)

️ The original title is ️

Departmental table Department:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+
(id, month)  Is the union primary key of the table .
 This form has information about the monthly income of each department .
 month (month) You can take the following values  ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

Write a SQL Query to reformat the table , Make the new table have a department id Columns and some correspondences Every month The income of the (revenue) Column .

The query result format is shown in the following example :

Department  surface :
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

 The result table obtained by query :
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

 Be careful , The result table has  13  Column  (1 Departments  id  Column  + 12 The revenue column for the last month ).

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
️ Their thinking ️

Pivot Function introduction

Through examination, we can find , You need to change the value of the month 12 Months into In the query results 12 Columns . This is the obvious Transfer line column format .

Oracle 11G New features emerging in Pivot The row to column function can solve this problem .

Let's take a look at Pivot function , It is mainly used for row column conversion .

The basic grammar is as follows :

SELECT ...
FROM   ...
PIVOT [XML]
   ( pivot_clause
     pivot_for_clause
     pivot_in_clause )
WHERE  ...

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

Now let's go straight through LeetCode Topic experiment to learn :

Create test table Department

CREATE TABLE department (
  id        NUMBER,
  revenue   NUMBER,
  month  VARCHAR2(10)
);

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

 Insert picture description here

Insert test data

INSERT INTO department VALUES (1, 8000, 'Jan');
INSERT INTO department VALUES (1, 7000, 'Feb');
INSERT INTO department VALUES (1, 6000, 'Mar');
INSERT INTO department VALUES (2, 9000, 'Jan');
INSERT INTO department VALUES (3, 10000, 'Feb');
commit;

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

 Insert picture description here

perform Pivot Row to column functions

According to the meaning : It has been determined that what needs to be found is listed as ID and 12 Month , The month column corresponds to REVENUE The value of needs to be summarized (SUM) Show .

SELECT *
FROM department
PIVOT (SUM(revenue) as "Revenue" for month in (
'Jan' as "Jan",
'Feb' as "Feb",
'Mar' as "Mar",
'Apr' as "Apr",
'May' as "May",
'Jun' as "Jun",
'Jul' as "Jul",
'Aug' as "Aug",
'Sep' as "Sep",
'Oct' as "Oct",
'Nov' as "Nov",
'Dec' as "Dec"
));

      
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

 Insert picture description here
The formatting result is consistent with the requirements of the original question , Problem solving completed .

 Insert picture description here

️ At the end

About Pivot Row to column functions , also UNPivot function , Interested friends can read the official documents , Or refer to the following article :

PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1


This sharing is over ~

If you think the article will help you , give the thumbs-up 、 Collection 、 Focus on 、 Comment on , One button four connection support , Your support is the biggest motivation for my creation .

版权声明
本文为[Lucifer, think twice]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/08/20210804210605385z.html

Scroll to Top