Oracle数据库行转列与列转行操作

Oracle数据库行转列和列转行

在Oracle数据库中，行转列（Pivot）和列转行（Unpivot）是常见的数据转换操作，用于改变数据的展示方式以满足不同的分析需求。

行转列（Pivot）

行转列是将多行数据转换为一行中的多列，通常用于将行中的值转换为列名。

基本语法

SELECT * FROM 
(
    SELECT 列1, 列2, 列3 
    FROM 表名
)
PIVOT 
(
    聚合函数(列3)
    FOR 列2 IN (值1 AS 别名1, 值2 AS 别名2, ...)
);

示例

假设有销售数据表SALES：

PRODUCT    MONTH    AMOUNT
-------    -----    ------
A          Jan      100
A          Feb      200
B          Jan      150
B          Feb      250

使用Pivot转换为列：

SELECT * FROM 
(
    SELECT PRODUCT, MONTH, AMOUNT 
    FROM SALES
)
PIVOT 
(
    SUM(AMOUNT)
    FOR MONTH IN ('Jan' AS JAN, 'Feb' AS FEB)
);

结果：

PRODUCT    JAN    FEB
-------    ---    ---
A          100    200
B          150    250

动态Pivot

Oracle 11g及以上版本支持动态Pivot：

DECLARE
  v_sql CLOB;
  v_columns CLOB;
BEGIN
  -- 获取动态列
  SELECT LISTAGG('''' || MONTH || ''' AS ' || MONTH, ',') WITHIN GROUP (ORDER BY MONTH)
  INTO v_columns
  FROM (SELECT DISTINCT MONTH FROM SALES);
  
  -- 构建动态SQL
  v_sql := 'SELECT * FROM 
            (SELECT PRODUCT, MONTH, AMOUNT FROM SALES)
            PIVOT
            (SUM(AMOUNT) FOR MONTH IN (' || v_columns || '))';
  
  -- 执行动态SQL
  EXECUTE IMMEDIATE v_sql;
END;
/

列转行（Unpivot）

列转行是将多列数据转换为多行数据，与Pivot操作相反。

基本语法

SELECT * FROM 表名
UNPIVOT 
(
    新值列名
    FOR 新列名列名 IN (列1 AS '值1', 列2 AS '值2', ...)
);

示例

使用上面Pivot后的结果表SALES_PIVOT：

PRODUCT    JAN    FEB
-------    ---    ---
A          100    200
B          150    250

使用Unpivot转换回行：

SELECT * FROM SALES_PIVOT
UNPIVOT 
(
    AMOUNT
    FOR MONTH IN (JAN AS 'Jan', FEB AS 'Feb')
);

结果：

PRODUCT    MONTH    AMOUNT
-------    -----    ------
A          Jan      100
A          Feb      200
B          Jan      150
B          Feb      250

动态Unpivot

Oracle也支持动态Unpivot操作：

DECLARE
  v_sql CLOB;
  v_columns CLOB;
BEGIN
  -- 获取动态列
  SELECT LISTAGG(COLUMN_NAME || ' AS ''' || COLUMN_NAME || '''', ',') 
         WITHIN GROUP (ORDER BY COLUMN_NAME)
  INTO v_columns
  FROM USER_TAB_COLUMNS
  WHERE TABLE_NAME = 'SALES_PIVOT' AND COLUMN_NAME != 'PRODUCT';
  
  -- 构建动态SQL
  v_sql := 'SELECT * FROM SALES_PIVOT
            UNPIVOT
            (AMOUNT FOR MONTH IN (' || v_columns || '))';
  
  -- 执行动态SQL
  EXECUTE IMMEDIATE v_sql;
END;
/

其他实现方式

在Oracle 11g之前的版本中，可以使用DECODE或CASE语句实现类似功能：

使用DECODE实现行转列

SELECT 
    PRODUCT,
    SUM(DECODE(MONTH, 'Jan', AMOUNT, 0)) AS JAN,
    SUM(DECODE(MONTH, 'Feb', AMOUNT, 0)) AS FEB
FROM SALES
GROUP BY PRODUCT;

使用UNION ALL实现列转行

SELECT PRODUCT, 'Jan' AS MONTH, JAN AS AMOUNT FROM SALES_PIVOT
UNION ALL
SELECT PRODUCT, 'Feb' AS MONTH, FEB AS AMOUNT FROM SALES_PIVOT;

注意事项

1. Pivot操作需要指定聚合函数，即使数据已经确保唯一
2. Unpivot操作会排除NULL值，除非使用INCLUDE NULLS选项
3. 动态Pivot/Unpivot需要使用PL/SQL实现
4. 对于大量数据，这些操作可能会有性能影响

行转列和列转行是数据分析中非常有用的技术，能够帮助用户以不同的视角查看和理解数据。