一条SQL完美实现Oracle中一行转多行（oracle 1行转多行）

一条SQL完美实现Oracle中一行转多行

在Oracle数据库中，我们经常会使用到行转列的操作，把一行数据按照某个字段进行分隔，转化成多行数据的形式。相反，有时候也会遇到需要将一行数据转化为多行数据的情况，这时候就需要用到行转多行操作。

一般来说，实现行转多行操作需要使用PL/SQL语言或是使用游标操作等复杂的方式才能完成。但是，在Oracle 11g之后，引入了UNPIVOT运算子，轻松将一行数据转化为多行数据，让操作变得简单易行。

下面是一个简单的例子，我们将一行数据转化为四行数据：

SELECT ID, COLUMN_NAME, COLUMN_VALUE
FROM (
  SELECT ID, COLUMN1, COLUMN2, COLUMN3, COLUMN4
  FROM YOUR_TABLE
  WHERE ID = 1
) UNPIVOT (
  COLUMN_VALUE FOR COLUMN_NAME IN (
    COLUMN1 AS 'COLUMN1',
    COLUMN2 AS 'COLUMN2',
    COLUMN3 AS 'COLUMN3',
    COLUMN4 AS 'COLUMN4'
  )
);

假设YOUR_TABLE表里面ID=1的记录包含COLUMN1~COLUMN4四个字段，字段值分别为“apple”、“orange”、“banana”、“watermelon”，则以上SQL语句将输出结果如下：

ID | COLUMN_NAME | COLUMN_VALUE
1  | COLUMN1    | apple
1  | COLUMN2    | orange
1  | COLUMN3    | banana
1  | COLUMN4    | watermelon

以上SQL语句采用了UNPIVOT运算子将多列数据转变为多行数据，实现了行转多行操作。

在上述例子中，我们把每一列名字和值都拼成了一个UNPIVOT子句中的一个字段名和值，这种方式虽然方便，但是当列名较多时，重复代码就会增多。为了避免这种情况，我们可以通过动态SQL的方式来构建UNPIVOT子句，把列名和值动态拼接成字符串。

以下是一个动态SQL的例子：

DECLARE
  SQL_STMT VARCHAR2(4000);
  COLUMN_LIST VARCHAR2(4000);
BEGIN
  SELECT LISTAGG('''' || COLUMN_NAME || ''' AS "' || COLUMN_NAME || '"', ',') WITHIN GROUP (ORDER BY COLUMN_ID)
  INTO COLUMN_LIST
  FROM ALL_TAB_COLUMNS
  WHERE TABLE_NAME = 'YOUR_TABLE'
    AND OWNER = 'YOUR_SCHEMA'
    AND COLUMN_NAME LIKE 'COLUMN%';

  SQL_STMT := 'SELECT ID, COLUMN_NAME, COLUMN_VALUE FROM (SELECT ID, ' || COLUMN_LIST || ' FROM YOUR_TABLE WHERE ID = 1) UNPIVOT (COLUMN_VALUE FOR COLUMN_NAME IN (' || COLUMN_LIST || '))';

  EXECUTE IMMEDIATE SQL_STMT;
END;

以上动态SQL语句中，通过ALL_TAB_COLUMNS视图查询出表YOUR_TABLE中所有以“COLUMN”开头的字段名，并使用LISTAGG函数将他们拼接成UNPIVOT语句中的字段名和值。通过EXECUTE IMMEDIATE函数执行完整的SQL语句，实现行转多行操作。

在Oracle数据库中，行转多行操作通常需要使用PL/SQL或者游标操作等复杂方式才能实现。而在Oracle 11g之后，引入UNPIVOT运算子简化了行转多行操作，让开发人员的工作变得更加轻松。