oracle convert column to multiple columns

Converting one column of data into multiple columns of data is a common requirement in Oracle. For example, let's say you have a table that contains products, sales dates, and sales quantities. You want to convert this into monthly sales for each product. The simplest way to achieve this goal is to use the PIVOT operation.

The following is a sample table:

PRODUCT   SALE_DATE   SALE_QUANTITY
-------------------------------------
A         2021-01-01  100
A         2021-01-15  200
A         2021-02-01  300
B         2021-01-01  150
B         2021-03-01  250

We hope to convert it into the following format:

PRODUCT   JAN_SALE  FEB_SALE  MAR_SALE
-------------------------------------
A         300       300       0
B         150       0         250

Use PIVOT to implement this operation, you can use the following SQL:

SELECT PRODUCT, 
       NVL(JAN_SALE, 0) AS JAN_SALE,
       NVL(FEB_SALE, 0) AS FEB_SALE,
       NVL(MAR_SALE, 0) AS MAR_SALE
FROM 
  (SELECT PRODUCT, 
          TO_CHAR(SALE_DATE, 'MON') AS SALE_MONTH, 
          SALE_QUANTITY
   FROM   SALES_TABLE)
PIVOT 
  (SUM(SALE_QUANTITY)
   FOR SALE_MONTH IN ('JAN' AS JAN_SALE, 'FEB' AS FEB_SALE, 'MAR' AS MAR_SALE))

In the above SQL, we first convert the original table into an intermediate result with a MONTH column. We used the TO_CHAR function to convert SALE_DATE into a short month string. We then use the PIVOT operator to convert each month's sales quantity into columns. The syntax of PIVOT is very concise, you only need to specify the columns and values ​​to be converted and the name of the new column.

In the above example, we used the NVL function to ensure that the results include all products, even if there are no sales in a month. If the NVL function is not used, the query results can only display products and months with sales.

The PIVOT operation is not only suitable for converting columns into multiple columns, but can also use similar syntax to convert rows into multiple rows. For example, let's say you have a table with regions, products, and sales quantities. You want to convert this into total sales per product and region. Using the PIVOT operation, you can easily achieve this goal.

In short, the PIVOT operation is a very useful and simple tool in Oracle SQL, which can quickly convert a column into multiple columns or a row into multiple rows. Its flexibility and customizable nature make it useful in many situations.

The above is the detailed content of oracle convert column to multiple columns. For more information, please follow other related articles on the PHP Chinese website!