gpt4 book ai didi

Oracle的数据表中行转列与列转行的操作实例讲解

转载 作者:qq735679552 更新时间:2022-09-29 22:32:09 26 4
gpt4 key购买 nike

CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章Oracle的数据表中行转列与列转行的操作实例讲解由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.

行转列 一张表 。

Oracle,数据表,行转列,列转行

查询结果为 。

Oracle,数据表,行转列,列转行

--行转列 。

?
1
2
3
4
select years,( select amount from Tb_Amount as A where month =1 and A.years=Tb_Amount.years) as m1,
( select amount from Tb_Amount as A where month =2 and A.years=Tb_Amount.years) as m2,
( select amount from Tb_Amount as A where month =3 and A.years=Tb_Amount.years) as m3
from Tb_Amount group by years

或者为 。

?
1
2
3
4
5
select years as 年份,
sum ( case when month = '1' then amount end ) as 一月,
  sum ( case when month = '2' then amount end ) as 二月,
sum ( case when month = '3' then amount end ) as 三月
from dbo.Tb_Amount group by years order by years desc

2.人员信息表包括姓名 时代  金额 。

Oracle,数据表,行转列,列转行

显示行转列 姓名     时代       金额 。

姓名  年轻         中年       老年 。

张丽 1000000.00 4000000.00    500000000.00 。

孙子 2000000.00   12233335.00  4552220010.00 。

Oracle,数据表,行转列,列转行

?
1
2
3
4
5
select uname as 姓名,
SUM ( case when era= '年轻' then amount end ) as 年轻,
SUM ( case when era= '中年' then amount end ) as 中年,
SUM ( case when era= '老年' then amount end ) as 老年
from Tb_People group by uname order by uname desc

 3.学生表 [Tb_Student] 。

Oracle,数据表,行转列,列转行

显示效果 。

Oracle,数据表,行转列,列转行

静态SQL,指subject只有语文、数学、英语这三门课程.

?
1
2
3
4
5
select sname as 姓名,
max ( case Subject when '语文' then grade else 0 end ) as 语文,
max ( case Subject when '数学' then grade else 0 end ) as 数学,
max ( case Subject when '英语' then grade else 0 end ) as 英语
from dbo.Tb_Student group by sname order by sname desc

--动态SQL,指subject不止语文、数学、英语这三门课程.

?
1
2
3
4
5
6
declare @sql varchar (8000)
set @sql = 'select sname as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ' '' + Subject + '' ' then grade else 0 end) [' + Subject + ']'
from ( select distinct Subject from Tb_Student) as a
set @sql = @sql + ' from Tb_Student group by sname order by sname desc'
exec (@sql)

oracle中Decode()函数使用 然后将这些累计求和(sum部分) 。

?
1
2
3
4
5
select t.sname AS 姓名,
sum (decode(t.subject, '语文' ,grade, null ))语文 ,
sum (decode(t.subject, '数学' ,grade, null )) 数学,
sum (decode(t.subject, '英语' ,grade, null )) 英语
from Tb_Student t group by sname order by sname desc

列转行 。

Oracle,数据表,行转列,列转行

生成 。

Oracle,数据表,行转列,列转行

sql代码 生成静态

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
select *
from ( select sname,[Course ] = '数学' ,[Score]=[数学] from Tb_students union all
select sname,[Course]= '英语' ,[Score]=[英语] from Tb_students union all
select sname,[Course]= '语文' ,[Score]=[语文] from Tb_students)t
order by sname, case [Course] when '语文' then 1 when '数学' then 2 when '英语' then 3 end
go
  --列转行的静态方案:UNPIVOT,sql2005及以后版本
 
  SELECT sname,Subject, grade
  from dbo.Tb_students
  unpivot(grade for Subject in ([语文],[数学],[英语])) as up
  GO
 
 
  --列转行的动态方案:UNPIVOT,sql2005及以后版本
  --因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行,同样也使用了XML处理。
  declare @s nvarchar(4000)
select @s= isnull (@s+ ',' , '' )+quotename( Name )
from syscolumns where ID=object_id( 'Tb_students' ) and Name not in ( 'sname' )
order by Colid
exec ( 'select sname,[Subject],[grade] from Tb_students unpivot ([grade] for [Subject] in(' +@s+ '))b' )
 
go
select
   sname,[Subject],[grade]
from
   Tb_students
unpivot
   ([grade] for [Subject] in ([数学],[英语],[语文]))b

  。

最后此篇关于Oracle的数据表中行转列与列转行的操作实例讲解的文章就讲到这里了,如果你想了解更多关于Oracle的数据表中行转列与列转行的操作实例讲解的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com