Oracle——行转列与列转行

文章目录

• 行转列
• • 创建表和增加测试数据
  • 方式一：先分组，再统计平铺
  • 方式二：使用Oracle11g自带函数PIVOT实现
• 列转行
• • 创建表和增加测试数据
  • 方式一：union all 单个合并
  • 方式二：unpivot 函数实现
• 总结
• 资料参考

把某一个字段的值作为唯一值，然后另外一个字段的行值转换成它的列值。

转换过程大致如下所示：

通常情况下，采取group by 唯一字段进行分组，然后依据不同的列进行判断输出就能转换。

创建表和增加测试数据

建表语句和增加测试数据sql如下所示：

create table XJ_TEST_LtoH(      stuid varchar(20),      stuname varchar(40),      coursename varchar(40),      score int);insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)values('sc202201001','张三','数学',40);insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)values('sc202201001','张三','语文',50);insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)values('sc202201001','张三','理综',120);insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)values('sc202201011','李四','数学',32);insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)values('sc202201011','李四','语文',45);insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)values('sc202201011','李四','理综',123);insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)values('sc202201031','王五','数学',54);insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)values('sc202201031','王五','语文',56);insert into XJ_TEST_LtoH(stuid,Stuname,coursename,score)values('sc202201031','王五','理综',100);

执行完毕后，数据库中当前的数据结构如下：

同所属的类，对应不同的值，采取不同列进行存储。

方式一：先分组，再统计平铺

将数据根据学员名分组，将学科和成绩平铺展示。sql如下所示：

-- 方式一：先分组，再单元拆分select t.stuid,t.stuname ,sum(decode(t.coursename,'数学',t.score,0)) "数学",sum(decode(t.coursename,'语文',t.score,0)) "语文",sum(decode(t.coursename,'理综',t.score,0)) "理综"from XJ_TEST_LtoH t group by t.stuname,t.stuid;

方式二：使用Oracle11g自带函数PIVOT实现

Oracle11g及以后自带函数PIVOT就能实现上述的效果，且代码量很小。

select * from 表PIVOT (sum(xxx ) for XXX    in(mm,nn) )

具体的使用方式，如下：

-- 方式二：Oracle11g之后提供了自带函数PIVOTselect * from XJ_TEST_LtoH pivot (      sum(score )       for      coursename        in('语文' as 语文,'数学' as 数学,'理综' as 理综) );

把一行当中的列的字段按照行的唯一值转换成多行数据。

还是上面的栗子，先创建测试表和增加测试数据。

创建表和增加测试数据

------ 列转行前的表创建create table XJ_TEST_HL as (select t.stuid,t.stuname ,sum(decode(t.coursename,'数学',t.score,0)) "数学",sum(decode(t.coursename,'语文',t.score,0)) "语文",sum(decode(t.coursename,'理综',t.score,0)) "理综"from XJ_TEST_LtoH t group by t.stuname,t.stuid);

这种语法，就能直接将查询到的数据信息，以及表结构中字段类型等信息，映射成一张新的表。

此时的数据结构如下所示：

方式一：union all 单个合并

查询每个需要拆分的列数据信息，以相同的列名接收，再将数据合并。

-- 方式一：先查询单个，再将所有数据拼接select * from (select t.stuid,t.stuname,'语文' as coursename,t.语文 as score from XJ_TEST_HL tunion allselect t.stuid,t.stuname,'数学' as coursename,t.数学 as score from XJ_TEST_HL tunion allselect t.stuid,t.stuname,'理综' as coursename,t.理综 as score from XJ_TEST_HL t) p order by p.stuname;

方式二：unpivot 函数实现

语法如下所示：

select 字段 from 数据集unpivot（自定义列名 for 自定义列名 in（列名））

【注意】这里的是 unpivot ，不是 pivot ！

-- 方式二-- 语法  select 字段 from 数据集 unpivot（自定义列名 for 自定义列名 in（列名））select * from XJ_TEST_HLunpivot (score for coursename in(语文,数学,理综));

pivot和unpivot不太好理解，并且属于oracle特有，针对别的类型数据库，可能方式一更好点。

oracle怎么实现行列转换

来源地址：https://blog.csdn.net/qq_38322527/article/details/127445099