SQL(横表和纵表)行列转换，PIVOT与UNPIVOT的区别和使用方法举例

pivot子句是Oracle database 11g的新增特性，可以在查询输出中将行旋转为列，同时对数据使用聚合函数。同时新增了unpivot子句，他可以在查询输出中将列旋转为行；

引入

现在有以下表数据：

 
（未显示完全。。。）

现在我们想观察(1, 2, 3)每种类型id在前四月的销售情况；

你可能会这么写sql：

select  prd_type_id, month, sum(amount) 
from all_sales where month <= 4 and prd_type_id in(1, 2, 3) group by month,prd_type_id order by prd_type_id;

但是这种显示结果也许并不清晰，想想如果把月份横着排列是不是更清晰，若用一般方法我们可以这样写：

select 
    prd_type_id, 
    sum(decode(month, 1, amount, 0)) as JAN, sum(decode(month, 2, amount, 0)) as FEB, sum(decode(month, 3, amount, 0)) as MAR, sum(decode(month, 4, amount, 0)) as APR from all_sales where prd_type_id in(1, 2, 3) group by prd_type_id; --不熟悉decode函数的可以用一下方法 select prd_type_id, sum(case when month = 1 then amount else 0 end) as JAN, sum(case when month = 2 then amount else 0 end) as FEB, sum(case when month = 3 then amount else 0 end) as MAR, sum(case when month = 4 then amount else 0 end) as APR from all_sales where prd_type_id in (1, 2, 3) group by prd_type_id;

这样显示是不是更好呢？但是现在oracle 11g为我们提供了更好的方法， 那就是pivot，他可以将行变成列：

select *
from (
-- ①
    select month, prd_type_id, amount from all_sales where year = 2003 and prd_type_id in(1, 2, 3) ) pivot( -- ② sum(amount) for month in (1 as JAN, 2 as FEB, 3 as MAR， 4 as APR) ) order by prd_type_id;

执行结果同上图； 
接下来，我将讲讲这些代码的含义（只是个人理解）：

对于①我的理解是，这部分限定了数据的范围，这个范围怎么确定呢，首先你得知道你想显示什么。在本文中，你需要显示prd_type_id, month, amount三种数据，所以就可以确定了；

对于②我的理解是，这部分规定了如何将行变成列；对于sum(amount)的含义，我们先放放，先说说这个month,他就规定了把什么变成从行变成列了，那么in后面的部分，则规定了，哪些month需要变成列，而as规定了别名，可以省略。那么对于sum(amount)怎么理解呢，首先他必须是聚合操作，他就是要在每一个month列下要显示的数据，拿一月来说，他下面的数据肯定不止一条，这也是为什么要使用聚合函数的原因了， 他到底对得是哪些amount的求和？我们知道在本题中，还有些列并未转成行，那么这些列同上面的月份便共同规定了对哪些amount求和了，如一月与id=1对应的那个格子，就是所有满足月份为一月，而类型id为1的数据了；

转换多个列

我们都知道for后面规定了哪些行将被转换为列，而in里面规定了要将哪些特定的行转换，有了这些，我们便不能理解下面的sql：

select *
from (
    select month, prd_type_id, amount from all_sales where year = 2003 and prd_type_id in(1,2,3) ) pivot ( sum(amount) for(month, prd_type_id) in( (1, 1) as jan_prd_type_1, (2, 2) as feb_prd_type_2, (3, 3) as mar_prd_type_3, (4, 2) as apr_prd_type_2 ) ); 

 

在转换中使用多个聚合函数

select  *
    from (
        select month, prd_type_id, amount from all_sales where year = 2003 and prd_type_id in(1, 2, 3) ) pivot ( sum(amount) as sum_amount, avg(amount) as avg_amount for(month) in( 1 as JAN, 2 as FEB ) ) order by prd_type_id;

 

值得注意的是系统会自动将两个别名合为一个；

unpivot子句

unpivot子句的作用是将列转换为行。

现有表格数据如下： 

select * from pivot_sales_data 
unpivot(
    amount for month in (jan, feb, mar, apr) ) order by prd_type_id;

 

转载于:https://www.cnblogs.com/pianai-shu/p/8256944.html