Oracle数据转换技巧：行转列与列转行

在数据管理和分析过程中，数据格式的转换是一项常见需求。本文将探讨如何在 Oracle 数据库中高效地进行行转列和列转行的操作。

### 1. 使用 PIVOT 函数进行行转列

PIVOT 函数是 Oracle 提供的一个强大的工具，用于将数据从行格式转换为列格式。下面是一个具体的例子：

假设我们有一个包含省份、城市和排名信息的表，原始数据如下所示：

通过使用 PIVOT 函数，我们可以轻松地将这些数据转换为以排名为列的形式：

with temp as (
    select '四川省' nation, '成都市' city, '第一' ranking from dual union all
    select '四川省' nation, '绵阳市' city, '第二' ranking from dual union all
    select '四川省' nation, '德阳市' city, '第三' ranking from dual union all
    select '四川省' nation, '宜宾市' city, '第四' ranking from dual union all
    select '湖北省' nation, '武汉市' city, '第一' ranking from dual union all
    select '湖北省' nation, '宜昌市' city, '第二' ranking from dual union all
    select '湖北省' nation, '襄阳市' city, '第三' ranking from dual
)
select * from (
    select nation, city, ranking from temp
) pivot (
    max(city) for ranking in ('第一' as 第一, '第二' as 第二, '第三' as 第三, '第四' as 第四)
);

转换后的结果如下图所示：

### 2. 使用 MAX 和 DECODE 函数进行行转列

除了 PIVOT 函数，我们还可以结合 MAX 和 DECODE 函数来实现行转列的功能。这种方法适用于较旧版本的 Oracle 数据库或特定场景下：

with temp as (
    select '四川省' nation, '成都市' city, '第一' ranking from dual union all
    select '四川省' nation, '绵阳市' city, '第二' ranking from dual union all
    select '四川省' nation, '德阳市' city, '第三' ranking from dual union all
    select '四川省' nation, '宜宾市' city, '第四' ranking from dual union all
    select '湖北省' nation, '武汉市' city, '第一' ranking from dual union all
    select '湖北省' nation, '宜昌市' city, '第二' ranking from dual union all
    select '湖北省' nation, '襄阳市' city, '第三' ranking from dual
)
select nation,
    max(decode(ranking, '第一', city, '')) as 第一,
    max(decode(ranking, '第二', city, '')) as 第二,
    max(decode(ranking, '第三', city, '')) as 第三,
    max(decode(ranking, '第四', city, '')) as 第四
from temp
group by nation;

### 3. 使用 MAX 和 CASE WHEN 函数进行行转列

CASE WHEN 语句提供了更灵活的条件判断方式，可以用来替代 DECODE 函数。例如，如果我们要根据年级和科目名称转换成绩数据，可以使用以下 SQL 语句：

select case when grade_id = '1' then '一年级'
            when grade_id = '2' then '二年级'
            when grade_id = '5' then '五年级'
            else null end "年级",r>       max(case when subject_name = '语文' then max_score else 0 end) "语文",
       max(case when subject_name = '数学' then max_score else 0 end) "数学",
       max(case when subject_name = '政治' then max_score else 0 end) "政治"
from dim_ia_test_ysf
group by case when grade_id = '1' then '一年级'
            when grade_id = '2' then '二年级'
            when grade_id = '5' then '五年级'
            else null end;

### 4. 使用 UNPIVOT 函数进行列转行

UNPIVOT 函数与 PIVOT 相反，它将列数据转换为行数据。例如，如果我们有一个包含省份和多个城市排名的表，可以通过 UNPIVOT 将其转换为每个城市的单独行：

with temp as (
    select '四川省' nation, '成都市' 第一, '绵阳市' 第二, '德阳市' 第三, '宜宾市' 第四 from dual union all
    select '湖北省' nation, '武汉市' 第一, '宜昌市' 第二, '襄阳市' 第三, '' 第四 from dual
)
select nation, name, title from
    temp
unpivot (name for title in (第一, 第二, 第三, 第四)) t;

通过上述方法，我们可以在 Oracle 数据库中灵活地进行数据格式的转换，以满足不同的业务需求。