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

1>pivot 列转行

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

?

1 2 3 4 5 6 7 8 9 10

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中的类型

?

1 2 3 4 5 6 7 8

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

 查询结果

传统写法

?

1 2 3 4 5

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关键字，表示任何列类型

?

1 2 3 4 5 6 7 8

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

查询结果如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

<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，

?

1 2 3 4 5 6 7 8

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

　　

 

2>unpivot 行转列

?

1 2 3 4 5

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);

查询结果

行转列

?

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

查询结果

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

 传统写法

?

1 2 3 4 5 6 7

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