oracle中行转列函数是什么

什么是行转列函数

行转列函数是指将一列数据转换为一行数据的函数，或者将一行数据转换为一列数据的函数。在Oracle数据库中，行转列函数有多种实现方式，例如使用PIVOT或UNPIVOT命令、使用CASE语句、使用XML函数等。

其中，PIVOT和UNPIVOT命令的使用较为简单，但是只能实现单个字段的转换，而使用CASE语句和XML函数可以实现多个字段的转换。

使用PIVOT实现行转列

PIVOT语法

PIVOT语法如下：

SELECT * 

FROM (
    SELECT column1, column2, column3 
    FROM table_name
)
PIVOT (
    aggregate_function(column1) 
    FOR column2 IN (value1, value2, ... value_n)
);

其中，column1为需要转换的列，column2为用于构建新表头的列，aggregate_function为指定对新表头所对应的数据进行计算的聚合函数（如SUM、AVG等），value1、value2、...value_n为构建新表头的值。

使用PIVOT实现单个字段的行转列

假设我们有以下一张表：

现在我们需要将该表中的日期列转换为新的表头，示例代码如下：

SELECT *

FROM (
    SELECT keyword, rank, to_char(date,'yyyy-mm-dd') as date
    FROM table_name
)
PIVOT (
    max(rank)
    FOR date IN ('2022-01-01', '2022-01-02', '2022-01-03')
);

执行以上代码，将得到以下结果：

可以看到，新的表头为日期列的值，表中的数据则为原表中对应日期下的rank值，且使用了max聚合函数取值。

使用PIVOT实现多个字段的行转列

如果需要将多个字段行转列，PIVOT命令的语法并不能满足需求，此时需要使用带有CASE语句的PIVOT语句来实现。

同样以上述表格为例，假设我们需要同时将keyword和rank两个字段的值行转列，示例代码如下：

SELECT *

FROM (
    SELECT keyword, rank, to_char(date,'yyyy-mm-dd') as date
    FROM table_name
)
PIVOT (
    max(rank) as rank, max(keyword) as keyword
    FOR date IN ('2022-01-01', '2022-01-02', '2022-01-03')
);

执行以上代码，将得到以下结果：

可以看到，结果表中共有6列（3个日期值，每个日期值对应一个keyword和rank值），每列的名称都由date列和聚合函数和需要转换的列组合而成。

使用UNPIVOT实现列转行

UNPIVOT语法

UNPIVOT语法如下：

SELECT *

FROM (
    SELECT column1, column2, column3, ...
    FROM table_name
)
UNPIVOT (
    value_column
    FOR unpivot_column IN (column2, column3, ...)
);

其中，value_column为需要转换为行的值所在的列，unpivot_column为需要转换为行的列。

使用UNPIVOT实现行转列

假设我们有以下一张表：

现在我们需要将该表中的2022-01-01、2022-01-02、2022-01-03三列转换为新的行，示例代码如下：

SELECT id, date, value

FROM (
    SELECT *
    FROM table_name
)
UNPIVOT (
    value FOR date IN ("2022-01-01", "2022-01-02", "2022-01-03")
);

执行以上代码，将得到以下结果：

可以看到，新的结果表中共有3列（id列、date列、value列），其中date列的值为原表中的2022-01-01、2022-01-02、2022-01-03三列。

使用CASE语句实现行转列

CASE语句的语法及使用

CASE语句是一种流程控制语句，使用它可以在SELECT语句中根据条件动态地生成数据。

CASE语句的基本语法如下：

CASE

    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    WHEN conditionn THEN resultn
    ELSE result
END

其含义为：当条件condition1成立时，返回结果result1；当条件condition2成立时，返回结果result2；......；当条件conditionn成立时，返回结果resultn；否则返回result。

使用CASE语句实现行转列

使用CASE语句实现行转列，需将需要转换为列的值作为条件进行判断，并配合SUM、MAX等聚合函数将同一种情况下的数据进行汇总。

假设我们有以下一张表：

现在我们需要将该表中的YEAR列的数据转换为新的列，示例代码如下：

SELECT NAME, 

    SUM(CASE WHEN YEAR=2020 THEN SALES ELSE 0 END) as "2020",
    SUM(CASE WHEN YEAR=2021 THEN SALES ELSE 0 END) as "2021"
FROM table_name
GROUP BY NAME;

执行以上代码，将得到以下结果：

可以看到，新的结果表中共有3列（NAME列、YEAR="2020"的SALES列和YEAR="2021"的SALES列），且每列中的数据为根据条件进行计算而来的汇总数据。

使用XML函数实现行转列

XML函数的语法及使用

Oracle数据库中可以使用XML函数将数据转换为XML格式，并使用XQUERY或XMLTABLE语句解析XML格式的数据。

XML函数的基本语法如下：

SELECT XMLAGG(

           XMLELEMENT(
                 NAME COLUMN, column_name
           )
)
FROM table_name;

其中，COLUMN为虚拟的XML元素名称，column_name为表中需要转换的列名。

使用XML函数实现行转列

使用XML函数实现行转列，需将需要转换的数据通过XML函数转换为XML格式，再使用XQUERY或XMLTABLE语句解析XML格式的数据，将XML元素作为新的列。

假设我们有以下一张表：

现在我们需要将该表中的2020、2021两列数据转换为新的列，示例代码如下：

SELECT *

FROM (
    SELECT name, XMLAGG(
                    XMLELEMENT(
                          NAME COLUMN, sales,"_",year
                    )
                ).EXTRACT('//text()').getclobval() as sales
    FROM (
        SELECT name, year, sales
        FROM table_name
    )
)
XMLTABLE ('for $t in /ROW/COLUMN return $t' 
          PASSING XMLTYPE(''||sales||''));

执行以上代码，将得到以下结果：

可以看到，新的结果表中共有3列（NAME列、2020_SALES列、2021_SALES列），且每列中的数据即为原表中对应数据拼接而成的字符串。

总结

行转列函数可以将一行数据转