Oracle行转列、列转行的Sql语句总结

1>pivot 列转行

语法： pivot（聚合函数 for 列名 in（类型）） ，其中 in('') 中可以指定别名

reate table demo(id int,name varchar(20),nums int);  
---- 创建表
insert into demo values(1, '苹果', 1000);
insert into demo values(2, '苹果', 2000);
insert into demo values(3, '苹果', 4000);
insert into demo values(4, '橘子', 5000);
insert into demo values(5, '橘子', 3000);
insert into demo values(6, '葡萄', 3500);
insert into demo values(7, '芒果', 4200);
insert into demo values(8, '芒果', 5500);

查询结果

1.1>指定类型的行专列，已知in中的类型

select * from (
   select name, nums 
   from demo
) 
pivot(
   sum(nums) 
   for name in('苹果' as "苹果1",'橘子','葡萄','芒果')
);

 查询结果

传统写法

select (select sum(nums) from demo a where a.name = '苹果') 苹果,
       (select sum(nums) from demo a where a.name = '橘子') 橘子,
       (select sum(nums) from demo a where a.name = '葡萄') 葡萄,
       (select sum(nums) from demo a where a.name = '芒果') 芒果
  from dual

　　

 1.2>不确定类型的行专列，未知in中的类型

如果in中不知道类型，则需要使用pivot xml，in中的类型使用any关键字，表示任何列类型

select * from (
   select name, nums 
   from demo t
) 
pivot xml(
   sum(nums) sumNums
   for name in(any)
)

查询结果如下：

<PivotSet>
    <item>
        <column name="NAME">橘子</column>
        <column name="SUMNUMS">8000</column>
    </item>
    <item>
        <column name="NAME">芒果</column>
        <column name="SUMNUMS">9700</column>
    </item>
    <item>
        <column name="NAME">苹果</column>
        <column name="SUMNUMS">7000</column>
    </item>
    <item>
        <column name="NAME">葡萄</column>
        <column name="SUMNUMS">3500</column>
    </item>
</PivotSet>

pivot xml，用子查询select distinct name from demo替代any，

select * from (
   select name, nums
   from demo t
)
pivot xml(
   sum(nums) sumNums
   for name in(select distinct name from demo)
)

　　

2>unpivot 行转列

create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);
insert into Fruit values(1,'苹果',1000,2000,3300,5000);
insert into Fruit values(2,'橘子',3000,3000,3200,1500);
insert into Fruit values(3,'香蕉',2500,3500,2200,2500);
insert into Fruit values(4,'葡萄',1500,2500,1200,3500);

查询结果

行转列

select id, name, jidu, xiaoshou
  from Fruit unpivot(xiaoshou for jidu in(q1, q2, q3, q4))

查询结果

注意： unpivot没有聚合函数，xiaoshou、jidu字段也是临时的变量

 传统写法

select '1' id, '苹果' name, 'Q1' jidu, (select q1 from fruit a where a.name = '苹果') xiaoshou from dual
union all
select '1' id, '苹果' name, 'Q2' jidu, (select q2 from fruit a where a.name = '苹果') xiaoshou from dual
union all
select '1' id, '苹果' name, 'Q3' jidu, (select q3 from fruit a where a.name = '苹果') xiaoshou from dual
union all
select '1' id, '苹果' name, 'Q4' jidu, (select q4 from fruit a where a.name = '苹果') xiaoshou from dual

参考：https://www.cnblogs.com/yewg/p/5510594.html