Oracle行转列、列转行实例

业务需求如下：

我们计算报表数据一般都是从清单表数据去汇总计算的，例如这个需求就是通过一条一条的订单根据区域日期等去区分汇总。报表计算这个很简单我这里就不叙述了。

下面看下普通计算出来的报表，想必大家的报表都是长这个样子的（这个是我随便写的数据，数据不重要，主要看如何转换成上述模板的样式）

--临时数据
--注意：此处为了字段清晰明了，我这里用中文做字段名，大家实际中不要学我，会有坑
with temp as(
select '手机' 业务,20210606 统计日, '全国' 区域,  138  日销量, 1055 周销量,  12138  月销量, 431055 年销量,  0.825508607198748 本周日均环比上月日均,  0.625508607198748 本月累计销量环比上月同期,  0.843121797398502  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华中' 区域,  662  日销量, 4171 周销量,  12662  月销量, 434171 年销量,  0.765040352164343 本周日均环比上月日均,  0.565040352164343 本月累计销量环比上月同期,  1.03725044225423   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华南' 区域,  583  日销量, 3275 周销量,  12583  月销量, 433275 年销量,  0.796255774373936 本周日均环比上月日均,  0.596255774373936 本月累计销量环比上月同期,  1.17303822937626   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华北' 区域,  278  日销量, 1481 周销量,  12278  月销量, 431481 年销量,  0.913633559531154 本周日均环比上月日均,  0.713633559531154 本月累计销量环比上月同期,  1.20835670218732   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华东' 区域,  384  日销量, 2616 周销量,  12384  月销量, 432616 年销量,  0.738774357526123 本周日均环比上月日均,  0.538774357526123 本月累计销量环比上月同期,  0.954840779658298  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '东北' 区域,  172  日销量, 1120 周销量,  12172  月销量, 431120 年销量,  0.634560906515581 本周日均环比上月日均,  0.434560906515581 本月累计销量环比上月同期,  0.963324299909666  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '西北' 区域,  193  日销量, 1202 周销量,  12193  月销量, 431202 年销量,  0.919663351185922 本周日均环比上月日均,  0.719663351185922 本月累计销量环比上月同期,  1.00117135207497   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '西南' 区域,  774  日销量, 4853 周销量,  12774  月销量, 434853 年销量,  0.892423685178374 本周日均环比上月日均,  0.692423685178374 本月累计销量环比上月同期,  1.01527525070875   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '全国' 区域,  138  日销量, 1055 周销量,  12138  月销量, 431055 年销量,  0.825508607198748 本周日均环比上月日均,  0.625508607198748 本月累计销量环比上月同期,  0.843121797398502  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华中' 区域,  662  日销量, 4171 周销量,  12662  月销量, 434171 年销量,  0.765040352164343 本周日均环比上月日均,  0.565040352164343 本月累计销量环比上月同期,  1.03725044225423   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华南' 区域,  583  日销量, 3275 周销量,  12583  月销量, 433275 年销量,  0.796255774373936 本周日均环比上月日均,  0.596255774373936 本月累计销量环比上月同期,  1.17303822937626   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华北' 区域,  278  日销量, 1481 周销量,  12278  月销量, 431481 年销量,  0.913633559531154 本周日均环比上月日均,  0.713633559531154 本月累计销量环比上月同期,  1.20835670218732   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华东' 区域,  384  日销量, 2616 周销量,  12384  月销量, 432616 年销量,  0.738774357526123 本周日均环比上月日均,  0.538774357526123 本月累计销量环比上月同期,  0.954840779658298  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '东北' 区域,  172  日销量, 1120 周销量,  12172  月销量, 431120 年销量,  0.634560906515581 本周日均环比上月日均,  0.434560906515581 本月累计销量环比上月同期,  0.963324299909666  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '西北' 区域,  193  日销量, 1202 周销量,  12193  月销量, 431202 年销量,  0.919663351185922 本周日均环比上月日均,  0.719663351185922 本月累计销量环比上月同期,  1.00117135207497   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '西南' 区域,  774  日销量, 4853 周销量,  12774  月销量, 434853 年销量,  0.892423685178374 本周日均环比上月日均,  0.692423685178374 本月累计销量环比上月同期,  1.01527525070875   本月日均环比上月日均 from dual
)

首先看到我们算出来的数据和模板的不一样之处，模板中的区域是行展示的，统计是列展示的，而我们的区域是列展示，统计是行展示的。这个时候我们就要先把统计字段列转行，再把区域字段行转列就可以得到我们想要的结果了

1.1.统计字段列转行

列转行Oracle提供了 unpivot 函数，但是好像要11g及以上，亲测9i不支持

unpivot(自定义列名/*列的值*/ for 自定义列名/*列名*/ in (列名))

--带入我们的SQL如下
with temp as(
select '手机' 业务,20210606 统计日, '全国' 区域,  138  日销量, 1055 周销量,  12138  月销量,  431055 年销量,  0.825508607198748 本周日均环比上月日均,  0.625508607198748 本月累计销量环比上月同期,  0.843121797398502  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华中' 区域,  662  日销量, 4171 周销量,  12662  月销量,  434171 年销量,  0.765040352164343 本周日均环比上月日均,  0.565040352164343 本月累计销量环比上月同期,  1.03725044225423   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华南' 区域,  583  日销量, 3275 周销量,  12583  月销量,  433275 年销量,  0.796255774373936 本周日均环比上月日均,  0.596255774373936 本月累计销量环比上月同期,  1.17303822937626   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华北' 区域,  278  日销量, 1481 周销量,  12278  月销量,  431481 年销量,  0.913633559531154 本周日均环比上月日均,  0.713633559531154 本月累计销量环比上月同期,  1.20835670218732   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华东' 区域,  384  日销量, 2616 周销量,  12384  月销量,  432616 年销量,  0.738774357526123 本周日均环比上月日均,  0.538774357526123 本月累计销量环比上月同期,  0.954840779658298  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '东北' 区域,  172  日销量, 1120 周销量,  12172  月销量,  431120 年销量,  0.634560906515581 本周日均环比上月日均,  0.434560906515581 本月累计销量环比上月同期,  0.963324299909666  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '西北' 区域,  193  日销量, 1202 周销量,  12193  月销量,  431202 年销量,  0.919663351185922 本周日均环比上月日均,  0.719663351185922 本月累计销量环比上月同期,  1.00117135207497   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '西南' 区域,  774  日销量, 4853 周销量,  12774  月销量,  434853 年销量,  0.892423685178374 本周日均环比上月日均,  0.692423685178374 本月累计销量环比上月同期,  1.01527525070875   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '全国' 区域,  138  日销量, 1055 周销量,  12138  月销量,  431055 年销量,  0.825508607198748 本周日均环比上月日均,  0.625508607198748 本月累计销量环比上月同期,  0.843121797398502  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华中' 区域,  662  日销量, 4171 周销量,  12662  月销量,  434171 年销量,  0.765040352164343 本周日均环比上月日均,  0.565040352164343 本月累计销量环比上月同期,  1.03725044225423   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华南' 区域,  583  日销量, 3275 周销量,  12583  月销量,  433275 年销量,  0.796255774373936 本周日均环比上月日均,  0.596255774373936 本月累计销量环比上月同期,  1.17303822937626   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华北' 区域,  278  日销量, 1481 周销量,  12278  月销量,  431481 年销量,  0.913633559531154 本周日均环比上月日均,  0.713633559531154 本月累计销量环比上月同期,  1.20835670218732   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华东' 区域,  384  日销量, 2616 周销量,  12384  月销量,  432616 年销量,  0.738774357526123 本周日均环比上月日均,  0.538774357526123 本月累计销量环比上月同期,  0.954840779658298  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '东北' 区域,  172  日销量, 1120 周销量,  12172  月销量,  431120 年销量,  0.634560906515581 本周日均环比上月日均,  0.434560906515581 本月累计销量环比上月同期,  0.963324299909666  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '西北' 区域,  193  日销量, 1202 周销量,  12193  月销量,  431202 年销量,  0.919663351185922 本周日均环比上月日均,  0.719663351185922 本月累计销量环比上月同期,  1.00117135207497   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '西南' 区域,  774  日销量, 4853 周销量,  12774  月销量,  434853 年销量,  0.892423685178374 本周日均环比上月日均,  0.692423685178374 本月累计销量环比上月同期,  1.01527525070875   本月日均环比上月日均 from dual
)
select * from temp
unpivot (value for 统计 in  (日销量,周销量,月销量,年销量,本周日均环比上月日均,本月累计销量环比上月同期,本月日均环比上月日均))t

1.2.现在我们看到统计字段已经列转行成功了，但是现在的区域还是行数据，我们再对区域进行行转列操作

行转列有三种种常见方法

--第一种就是unpivot对应的 pivot 方法
pivot(聚合函数 for 列名 in(类型))

--第二种就是利用 decode函数
with temp as(
select '手机' 业务,20210606 统计日, '全国' 区域,  138  日销量, 1055 周销量,  12138  月销量,  431055 年销量,  0.825508607198748 本周日均环比上月日均,  0.625508607198748 本月累计销量环比上月同期,  0.843121797398502  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华中' 区域,  662  日销量, 4171 周销量,  12662  月销量,  434171 年销量,  0.765040352164343 本周日均环比上月日均,  0.565040352164343 本月累计销量环比上月同期,  1.03725044225423   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华南' 区域,  583  日销量, 3275 周销量,  12583  月销量,  433275 年销量,  0.796255774373936 本周日均环比上月日均,  0.596255774373936 本月累计销量环比上月同期,  1.17303822937626   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华北' 区域,  278  日销量, 1481 周销量,  12278  月销量,  431481 年销量,  0.913633559531154 本周日均环比上月日均,  0.713633559531154 本月累计销量环比上月同期,  1.20835670218732   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华东' 区域,  384  日销量, 2616 周销量,  12384  月销量,  432616 年销量,  0.738774357526123 本周日均环比上月日均,  0.538774357526123 本月累计销量环比上月同期,  0.954840779658298  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '东北' 区域,  172  日销量, 1120 周销量,  12172  月销量,  431120 年销量,  0.634560906515581 本周日均环比上月日均,  0.434560906515581 本月累计销量环比上月同期,  0.963324299909666  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '西北' 区域,  193  日销量, 1202 周销量,  12193  月销量,  431202 年销量,  0.919663351185922 本周日均环比上月日均,  0.719663351185922 本月累计销量环比上月同期,  1.00117135207497   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '西南' 区域,  774  日销量, 4853 周销量,  12774  月销量,  434853 年销量,  0.892423685178374 本周日均环比上月日均,  0.692423685178374 本月累计销量环比上月同期,  1.01527525070875   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '全国' 区域,  138  日销量, 1055 周销量,  12138  月销量,  431055 年销量,  0.825508607198748 本周日均环比上月日均,  0.625508607198748 本月累计销量环比上月同期,  0.843121797398502  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华中' 区域,  662  日销量, 4171 周销量,  12662  月销量,  434171 年销量,  0.765040352164343 本周日均环比上月日均,  0.565040352164343 本月累计销量环比上月同期,  1.03725044225423   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华南' 区域,  583  日销量, 3275 周销量,  12583  月销量,  433275 年销量,  0.796255774373936 本周日均环比上月日均,  0.596255774373936 本月累计销量环比上月同期,  1.17303822937626   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华北' 区域,  278  日销量, 1481 周销量,  12278  月销量,  431481 年销量,  0.913633559531154 本周日均环比上月日均,  0.713633559531154 本月累计销量环比上月同期,  1.20835670218732   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华东' 区域,  384  日销量, 2616 周销量,  12384  月销量,  432616 年销量,  0.738774357526123 本周日均环比上月日均,  0.538774357526123 本月累计销量环比上月同期,  0.954840779658298  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '东北' 区域,  172  日销量, 1120 周销量,  12172  月销量,  431120 年销量,  0.634560906515581 本周日均环比上月日均,  0.434560906515581 本月累计销量环比上月同期,  0.963324299909666  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '西北' 区域,  193  日销量, 1202 周销量,  12193  月销量,  431202 年销量,  0.919663351185922 本周日均环比上月日均,  0.719663351185922 本月累计销量环比上月同期,  1.00117135207497   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '西南' 区域,  774  日销量, 4853 周销量,  12774  月销量,  434853 年销量,  0.892423685178374 本周日均环比上月日均,  0.692423685178374 本月累计销量环比上月同期,  1.01527525070875   本月日均环比上月日均 from dual
)
select
    nvl(decode(tmp.区域, '全国', tmp.日销量), 0) as 日销量,
    nvl(decode(tmp.区域, '全国', tmp.周销量), 0) as 周销量,
    nvl(decode(tmp.区域, '全国', tmp.月销量), 0) as 月销量,
    nvl(decode(tmp.区域, '全国', tmp.年销量), 0) as 年销量,
    nvl(decode(tmp.区域, '全国', tmp.本周日均环比上月日均), 0) as 本周日均环比上月日均
    --等等等等，我就不写了
from temp

--第三种就是case when去一种情况一种情况的写了，这里我也不写了

最终结果表语句如下：

with temp as(
select '手机' 业务,20210606 统计日, '全国' 区域,  138  日销量, 1055 周销量,  12138  月销量,  431055 年销量,  0.825508607198748 本周日均环比上月日均,  0.625508607198748 本月累计销量环比上月同期,  0.843121797398502  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华中' 区域,  662  日销量, 4171 周销量,  12662  月销量,  434171 年销量,  0.765040352164343 本周日均环比上月日均,  0.565040352164343 本月累计销量环比上月同期,  1.03725044225423   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华南' 区域,  583  日销量, 3275 周销量,  12583  月销量,  433275 年销量,  0.796255774373936 本周日均环比上月日均,  0.596255774373936 本月累计销量环比上月同期,  1.17303822937626   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华北' 区域,  278  日销量, 1481 周销量,  12278  月销量,  431481 年销量,  0.913633559531154 本周日均环比上月日均,  0.713633559531154 本月累计销量环比上月同期,  1.20835670218732   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '华东' 区域,  384  日销量, 2616 周销量,  12384  月销量,  432616 年销量,  0.738774357526123 本周日均环比上月日均,  0.538774357526123 本月累计销量环比上月同期,  0.954840779658298  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '东北' 区域,  172  日销量, 1120 周销量,  12172  月销量,  431120 年销量,  0.634560906515581 本周日均环比上月日均,  0.434560906515581 本月累计销量环比上月同期,  0.963324299909666  本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '西北' 区域,  193  日销量, 1202 周销量,  12193  月销量,  431202 年销量,  0.919663351185922 本周日均环比上月日均,  0.719663351185922 本月累计销量环比上月同期,  1.00117135207497   本月日均环比上月日均 from dual union all
select '手机' 业务,20210606 统计日, '西南' 区域,  774  日销量, 4853 周销量,  12774  月销量,  434853 年销量,  0.892423685178374 本周日均环比上月日均,  0.692423685178374 本月累计销量环比上月同期,  1.01527525070875   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '全国' 区域,  138  日销量, 1055 周销量,  12138  月销量,  431055 年销量,  0.825508607198748 本周日均环比上月日均,  0.625508607198748 本月累计销量环比上月同期,  0.843121797398502  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华中' 区域,  662  日销量, 4171 周销量,  12662  月销量,  434171 年销量,  0.765040352164343 本周日均环比上月日均,  0.565040352164343 本月累计销量环比上月同期,  1.03725044225423   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华南' 区域,  583  日销量, 3275 周销量,  12583  月销量,  433275 年销量,  0.796255774373936 本周日均环比上月日均,  0.596255774373936 本月累计销量环比上月同期,  1.17303822937626   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华北' 区域,  278  日销量, 1481 周销量,  12278  月销量,  431481 年销量,  0.913633559531154 本周日均环比上月日均,  0.713633559531154 本月累计销量环比上月同期,  1.20835670218732   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '华东' 区域,  384  日销量, 2616 周销量,  12384  月销量,  432616 年销量,  0.738774357526123 本周日均环比上月日均,  0.538774357526123 本月累计销量环比上月同期,  0.954840779658298  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '东北' 区域,  172  日销量, 1120 周销量,  12172  月销量,  431120 年销量,  0.634560906515581 本周日均环比上月日均,  0.434560906515581 本月累计销量环比上月同期,  0.963324299909666  本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '西北' 区域,  193  日销量, 1202 周销量,  12193  月销量,  431202 年销量,  0.919663351185922 本周日均环比上月日均,  0.719663351185922 本月累计销量环比上月同期,  1.00117135207497   本月日均环比上月日均 from dual union all
select '电脑' 业务,20210606 统计日, '西南' 区域,  774  日销量, 4853 周销量,  12774  月销量,  434853 年销量,  0.892423685178374 本周日均环比上月日均,  0.692423685178374 本月累计销量环比上月同期,  1.01527525070875   本月日均环比上月日均 from dual
)
select * from temp
unpivot (value for 统计 in  (日销量,周销量,月销量,年销量,本周日均环比上月日均,本月累计销量环比上月同期,本月日均环比上月日均))t
pivot (max(value) for 区域 in ('全国' as 全国,'华中' AS 华中,'华南' AS 华南,'华北' AS 华北,'华东' AS 华东,'东北' AS 东北,'西北' AS 西北,'西南' AS 西南))
order by 业务,统计 desc

大功告成，其实pivot、unpivot两个函数并不难，主要是要理清楚哪个字段要去行转列、哪个字段列转行才能转出想要的结果，其实尝试尝试就容易理解啦！加油！