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 列转换为行，并进行聚合计算