Oracle 行轉列函數詳解

Oracle 行轉列是一種數據轉換方式，它可以將數據從以行為單位的形式，轉換為以列為單位的形式。這種轉換方式可以在數據處理中發揮重要作用，尤其在需要匯總、分析和統計數據時，它可以幫助我們更快地獲取需要的數據，從而提升工作效率。在本文中，我們將詳細講解 Oracle 行轉列函數的相關知識，包括不同類型的函數、函數的用法、以及函數在實際應用中的案例說明。

一、Oracle 行轉列函數 WM

Oracle 行轉列函數 WM 是 Oracle 數據庫中的一種行轉列函數，它可以將以行形式存儲的數據轉換為以列形式存儲的數據，方便後續的處理。 另外，WM 函數還支持將多列數據合併為一列，以逗號分割。

以下為 WM 函數的基本語法：

SELECT WM_CONCAT(expression)
FROM table_name
[WHERE conditions];

其中，expression 表示待處理的列名或者表達式，table_name 表示待處理的表名。如果需要過濾數據，則可以通過 WHERE 子句指定過濾條件。

WM 函數的實際應用場景很廣泛，例如在員工考勤管理系統中，可以利用 WM 函數將某員工月度考勤記錄合併為一行，並將每天的考勤結果以逗號分割，便於上級領導進行查看和統計。

二、Oracle 行轉列函數 LIST

Oracle 行轉列函數 LIST 可以將一組數據轉換為以列形式存儲的數據，並且可以將多行數據合併為一行。與 WM 函數類似，LIST 函數也能夠方便地進行數據處理，提高數據處理效率。

以下是 LIST 函數的基本語法：

SELECT LISTAGG(expression, ',' ) WITHIN GROUP (ORDER BY column_name)
FROM table_name;

其中，expression 表示待處理的列名或者表達式，table_name 表示待處理的表名，column_name 用來指定按照哪一列來進行排序。需要注意的是，在使用 LIST 函數時，要先使用 ORDER BY 子句進行排序。

LIST 函數在實際應用中也十分常見，例如在某個在線商城的訂單管理系統中，可以使用 LIST 函數將某個用戶的多個訂單的訂單號、訂單金額、訂單狀態等數據信息合併為一條記錄，並將這些信息以逗號分割展示給用戶，方便用戶查看。

三、Oracle 行轉列函數 PIVOT 例子

Oracle 行轉列函數 PIVOT 不僅可以實現行轉列的功能，還可以進行聚合，如求和、平均值等操作。 以下示例展示了如何使用 PIVOT 函數將行數據轉換為列形式，並進行聚合計算：

SELECT *
FROM (
   SELECT sales_rep, sales_month, sales_amt
   FROM sales
)
PIVOT (
   SUM(sales_amt) FOR sales_month
   IN ('JAN' AS JANUARY, 'FEB' AS FEBRUARY, 'MAR' AS MARCH)
)
ORDER BY sales_rep;

在上述語句中，我們首先指定要查詢的列，即銷售代表的名稱、銷售月份以及銷售總金額。然後，通過 PIVOT 函數指定需要進行聚合計算的列參數，該函數的語法為：PIVOT (aggregate_expression FOR column_name IN (val1, val2, …, valn))。在此例中我們使用了 SUM 聚合函數來計算銷售總金額，聚合參數為 sales_amt，按照 sales_month 進行分組。最後，我們通過 ORDER BY 子句指定了銷售代表的名稱來進行排序，從而按照姓名順序展示數據。

四、Oracle 行轉列函數 PIVOT

PIVOT 函數是 Oracle 行轉列函數中最常用的一種，它可以將以行形式存儲的數據轉換為以列形式存儲的數據，並且進行聚合計算。

以下是 PIVOT 函數的基本語法：

SELECT *
FROM (
   SELECT column_name, row_name, value
   FROM table_name
)
PIVOT (
    aggregate_function(value)
    FOR column_name
    IN (column_value1, column_value2, … , column_value_n)
)

在使用 PIVOT 函數時，我們需要指定查詢的列名、行名和值，然後使用 PIVOT 子句將行數據轉換為列數據，並進行聚合計算。其中，column_name 代表列名，row_name 代表行名，value 代表值。在 IN 子句中，我們需要指定需要進行轉換的列的值，以及需要進行計算的值字段。最後再通過 ORDER BY 子句指定排序方式，即可方便地實現行轉列操作。

五、Oracle 行轉列函數 聚合

Oracle 行轉列函數中的聚合操作，可以進一步提高數據處理的效率，並且方便了數據的計算和統計。下面是一組使用 Oracle 聚合函數進行行轉列的示例：

SELECT *
FROM (
     SELECT product, color, size, quantity
     FROM Sales
)
PIVOT (
     SUM(quantity)
     FOR (color, size)
     IN(('RED', 'S') AS RS, ('RED', 'M') AS RM, ('RED', 'L') AS RL,('BLUE', 'S') AS BS,('BLUE', 'M') AS BM,('BLUE', 'L') AS BL )
)
WHERE product = 'PHONE';

在上述示例中，我們使用了 SUM 聚合函數對數量進行求和操作，然後使用 PIVOT 函數將原始行數據轉化為列數據，其中，在 IN 子句中指定了需要轉換的列值，以及對應的聚合字段。最後使用 WHERE 子句對產品進行篩選，以便得到所需的數據。

六、Oracle 行轉列函數並排序

如果需要將行數據轉換為列數據，並按照某種規則進行排序，可以利用 Oracle 行轉列函數進行操作。

以下是一個示例：

SELECT EMPNO,
       NVL(JAN, 0) AS JANUARY,
       NVL(FEB, 0) AS FEBRUARY,
       NVL(MAR, 0) AS MARCH,
       NVL(APR, 0) AS APRIL,
       NVL(MAY, 0) AS MAY,
       NVL(JUN, 0) AS JUNE
FROM (
  SELECT EMPNO, TO_CHAR(SALE_DATE, 'MON') SALE_MONTH, SALE_AMOUNT
  FROM SALES
)
PIVOT (
  SUM(SALE_AMOUNT)
  FOR SALE_MONTH IN('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN')
)
ORDER BY EMPNO;

在此例中，我們首先使用 SELECT 語句從 SALES 表中獲取 EMPNO（員工號）、SALE_DATE（銷售日期）和 SALE_AMOUNT（銷售總額）三列數據，然後使用 TO_CHAR 函數將銷售日期轉換為月份（JAN，FEB，MAR 等）形式。接着，我們使用 PIVOT 函數將行數據轉換為列數據，並進行 SUM 聚合計算。最後通過 ORDER BY 子句對EMPNO 進行排序，以便按照員工號排列展示數據。

七、Oracle 列轉行函數

Oracle 列轉行函數與行轉列函數相反，它可以將以列為單位存儲的數據轉換為以行為單位存儲的數據。以下是 Oracle 列轉行函數的語法格式：

SELECT "column1", "column2", "column3"
FROM
(
  SELECT "column1", "column2", "column3"
  FROM table_name
)
UNPIVOT
(
  "value"
  FOR "column_name" IN ("column2", "column3")
)

其中，UNPIVOT 操作將列轉換為行，將指定的列轉換為值並使用列名作為標識符。在 SELECT 語句中，我們需要指定需要查詢的列名。在 UNPIVOT 子句中，我們則需要指定待轉換的列名，以及用於標識值的列名。這個例子可以很好地將相關的列轉換為單個的值來展示數據。

八、Oracle 行轉列列轉行

Oracle 行轉列列轉行是一種相對複雜的數據轉換操作，需要根據實際需求進行靈活處理。以下是一組 Oracle 行轉列列轉行示例，通過這些示例，我們可以更好地理解如何使用 Oracle 行轉列函數實現數據轉換：

1、行轉列示例：

SELECT *
FROM (
  SELECT PRODUCT, COLOR, SIZE, SALES
  FROM SALES
)
PIVOT (
  SUM(SALES)
  FOR COLOR
  IN ('RED' AS RED, 'BLUE' AS BLUE)
)
ORDER BY PRODUCT, SIZE;

在此示例中，我們將原始行數據轉換為列形式數據，並進行了聚合計算，使用了 PIVOT 函數將 COLOR 列值轉換為對應的列名，以便進行統計和分析。

2、列轉行示例：

SELECT customer,
       order_date,
       part_item,
       part_qty
FROM (
  SELECT customer, order_date, part_a, part_b, part_c
  FROM orders
)
UNPIVOT (
  part_qty
  FOR part_item
  IN (part_a, part_b, part_c)
)
ORDER BY customer, order_date;

在此示例中，我們將列值轉換為行數據，並使用 UNPIVOT 函數將 part_a、part_b 和 part_c 轉換為行名，以便後續的處理。

九、Oracle 行轉列查詢

在實際應用場景中，我們常常需要根據需求進行不同類型的 Oracle 行轉列操作。因此，如何根據不同的數據處理需求選擇合適的數據轉換方式，是使用行轉列函數的關鍵所在。以下是一組 Oracle 行轉列查詢的示例，以供參考：

1、將 rows 列匯總為 column ，使用 LIST 函數：

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS Employees
FROM emp
GROUP BY deptno
ORDER BY deptno;

在此示例中，我們將 emp 表中的 rows 列匯總為單個 column 列，並使用 LIST 函數對名字進行排序，並以逗號分隔展示。

2、將多個 columns 列轉換為行，並展示出每個員工的工資明細：

SELECT e.empno, e.ename, s.sal_type, s.sal_amount
FROM emp e
LEFT OUTER JOIN sal s ON e.empno = s.empno
UNPIVOT
(
  sal_amount
  FOR sal_type
  IN (s.basic_salary AS 'BASIC SALARY',
      s.annual_bonus AS 'ANNUAL BONUS',
      s.overtime_pay AS 'OVERTIME PAY')
)
ORDER BY e.empno, sal_type;

在此示例中，我們將 emp 表中的多列信息轉換為行展示，包括每位員工的基本工資、年度獎金和加班工資情況。

3、將 columns 列轉換為行，並進行聚合計算