Oracle数据库中的动态行转列操作--Pivot函数

点击上方蓝字关注我们

在Oracle数据库中处理数据时，经常会遇到将行数据转换为列数据的需求，尤其是在生成报表或进行数据分析时。Oracle的Pivot函数提供了一个非常强大的工具来实现这一转换。本文将详细介绍如何使用Pivot函数进行动态行转列操作，并提供一个具体的操作示例。

什么是Pivot函数？

Pivot函数的逻辑

使用pl/sql实现“行”转“列”

CREATE TABLE sales_data (
    sale_year NUMBER(4),         -- 销售年份
    product_id NUMBER(10),       -- 产品ID
    product_name VARCHAR2(100),  -- 产品名称
    sales_amount NUMBER(10, 2)   -- 销售金额
);


INSERT INTO sales_data (sale_year, product_id, product_name, sales_amount) VALUES (2021, 101, 'Laptop', 1500);
INSERT INTO sales_data (sale_year, product_id, product_name, sales_amount) VALUES (2021, 102, 'Smartphone', 1200);
INSERT INTO sales_data (sale_year, product_id, product_name, sales_amount) VALUES (2022, 101, 'Laptop', 1800);
INSERT INTO sales_data (sale_year, product_id, product_name, sales_amount) VALUES (2022, 102, 'Smartphone', 1300);


复制

DECLARE
  pivot_cols VARCHAR2(4000);
  v_sql VARCHAR2(4000);
  cursor_id INTEGER;
  return_value INTEGER;
  col_count INTEGER;
  desc_tab DBMS_SQL.DESC_TAB;
  v_product_name VARCHAR2(100);
  v_sales NUMBER;


BEGIN
  -- 生成动态列
  SELECT LISTAGG('SUM(CASE WHEN sale_year = ' || sale_year || ' THEN sales_amount ELSE 0 END) AS "' || sale_year || '"', ', ')
    WITHIN GROUP (ORDER BY sale_year)
  INTO pivot_cols
  FROM (SELECT DISTINCT sale_year FROM sales_data);


  -- 构建完整的SQL查询
  v_sql := 'SELECT product_name, ' || pivot_cols || ' FROM sales_data GROUP BY product_name';


  -- 准备和执行SQL
  cursor_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cursor_id, v_sql, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(cursor_id, col_count, desc_tab);


  -- 定义列
  DBMS_SQL.DEFINE_COLUMN(cursor_id, 1, v_product_name, 100);
  FOR i IN 2 .. col_count LOOP
    DBMS_SQL.DEFINE_COLUMN(cursor_id, i, v_sales);
  END LOOP;


  -- 执行并获取结果
  return_value := DBMS_SQL.EXECUTE(cursor_id);


  -- 获取结果
  WHILE DBMS_SQL.FETCH_ROWS(cursor_id) > 0 LOOP
    DBMS_SQL.COLUMN_VALUE(cursor_id, 1, v_product_name);
    DBMS_OUTPUT.PUT(v_product_name || '
: 
');
    FOR i IN 2 .. col_count LOOP
      DBMS_SQL.COLUMN_VALUE(cursor_id, i, v_sales);
      DBMS_OUTPUT.PUT(v_sales || '
');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;
  END LOOP;


  -- 关闭游标
  DBMS_SQL.CLOSE_CURSOR(cursor_id);
END;
/
复制

### 变量声明
- `pivot_cols`: 存储动态生成的聚合函数和列名的字符串。
- `v_sql`: 存储完整的SQL查询字符串。
- `cursor_id`: 存储打开的游标ID。
- `return_value`: 存储执行SQL语句后的返回值。
- `col_count`: 存储描述表中的列数。
- `desc_tab`: 存储列的描述信息。
- `v_product_name`: 用于存储从结果集中获取的产品名称。
- `v_sales`: 用于存储从结果集中获取的销售额。


### 代码逻辑


1. **生成动态列**:
   - 使用`LISTAGG`函数和`CASE`语句结合，为每一个独特的`sale_year`生成一个聚合列，这些列将显示每个产品每年的总销售额。
   - 生成的每一列都是一个`SUM`聚合函数，计算当年的`sales_amount`，如果当前行的年份不匹配，则结果为0。


2. **构建完整的SQL查询**:
   - 构建一个包含所有动态生成列的SQL查询语句，这个查询将按产品名称分组，为每个产品计算每年的销售总额。


3. **准备和执行SQL**:
   - 打开一个新的游标。
   - 使用`DBMS_SQL.PARSE`函数解析并准备之前构建的SQL查询。
   - 使用`DBMS_SQL.DESCRIBE_COLUMNS`函数描述查询结果中的列，并初始化列计数和列描述信息。


4. **定义结果集中的列**:
   - 为结果集中的每一列定义变量，以便从游标中获取数据。第一列是产品名称，其余列是每年的销售额。


5. **执行查询并获取结果**:
   - 执行查询并通过循环读取每一行结果，对于每一行，首先获取产品名称，然后依次获取每一年的销售额。
   - 使用`DBMS_OUTPUT.PUT`函数输出每个产品的名称和对应年份的销售额。


6. **关闭游标**:
   - 在获取所有数据后，关闭游标以释放资源。
复制

使用PIVOT函数

SELECT *
FROM (
  SELECT sale_year, product_name, sales_amount
  FROM sales_data
)
PIVOT (
  SUM(sales_amount)
  FOR sale_year IN (2021, 2022)
) ORDER BY product_name;
复制

内部查询选择了需要进行转置的列。
PIVOT操作执行实际的行转列转换，其中SUM(sales_amount)是聚合函数，sale_year是需要转置的列。
FOR sale_year IN (2021, 2022)定义了需要转换成列的具体值。
复制

总 结

    PIVOT函数是Oracle SQL中处理数据转换非常有效的工具。通过适当地使用它，可以大大简化数据分析和报表制作的过程。希望本文的介绍能帮助你在实际工作中更好地应用这一功能。