Oracle 12c中书写数据时行变列，列变行（oracle12列变行）

Oracle 12c is an advanced version of the popular database management system, adding new features and improvements to its predecessor, Oracle 11g. One of the standout features of Oracle 12c is the ability to pivot rows into columns or columns into rows.

Before the introduction of this feature, transforming rows into columns or vice versa required complex SQL queries or the use of third-party tools. But now, with Oracle 12c, it’s as easy as a few lines of code.

Let’s take a closer look at how this transformation works through an example.

Suppose we have a table called “sales” with the following columns: “month”, “product”, and “sales_amount”. We want to create a report that shows the sales for each product by month, with each month displayed as a separate column.

To achieve this transformation, we use the “pivot” keyword in our SQL statement. Here’s an example of the code:

SELECT *
FROM sales
PIVOT (SUM(sales_amount) FOR month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'));

This code will return a table with the product names as rows and the sales for each month as columns. The data will be aggregated using the SUM function.

But what if we want to transform columns into rows instead? This is also possible with Oracle 12c’s “unpivot” functionality. Let’s take a look at an example.

Suppose we have a table called “employee_sales” with the following columns: “employee_id”, “product”, “Jan_sales”, “Feb_sales”, and “Mar_sales”. We want to create a report that shows the sales for each employee, with each month displayed as a separate row.

To achieve this transformation, we use the “unpivot” keyword in our SQL statement. Here’s an example of the code:

SELECT employee_id, month, sales
FROM employee_sales
UNPIVOT (sales FOR month IN (Jan_sales, Feb_sales, Mar_sales));

This code will return a table with the employee IDs as rows and the sales for each month as columns. The data will be “unpivoted” using the “sales” column and displayed as separate rows for each month.

In conclusion, Oracle 12c’s ability to pivot rows into columns or columns into rows is a powerful feature that simplifies data transformations and reporting. Whether you need to transform your data to display it in a more readable format or manipulate it for analytics, this functionality makes it easy and efficient to do so.