玩转-SQL2005数据库行列转换
数据库  /  管理员 发布于 5年前   104
注意:列转行的方法可能是我独创的了,呵呵,因为在网上找不到哦,全部是我自己写的,用到了系统的SysColumns
(一)行转列的方法
先说说行转列的方法,这个就比较好想了,利用拼sql和case when解决即可
实现目的
1:建立测试用的数据库
复制代码 代码如下:
CREATE TABLE RowTest(
[Name] [nvarchar](10) NULL,--名Q
[Course] [nvarchar](10) NULL,--n程名Q
[Record] [int] NULL--n程的分
)
2:加入测试用的数据库(先加入整齐的数据)
复制代码 代码如下:
insert into RowTest values ('张三','语文','91')
insert into RowTest values ('张三','数学','92')
insert into RowTest values ('张三','英语','93')
insert into RowTest values ('张三','生物','94')
insert into RowTest values ('张三','物理','95')
insert into RowTest values ('张三','化学','96')
insert into RowTest values ('李四','语文','81')
insert into RowTest values ('李四','数学','82')
insert into RowTest values ('李四','英语','83')
insert into RowTest values ('李四','生物','84')
insert into RowTest values ('李四','物理','85')
insert into RowTest values ('李四','化学','86')
insert into RowTest values ('小生','语文','71')
insert into RowTest values ('小生','数学','72')
insert into RowTest values ('小生','英语','73')
insert into RowTest values ('小生','生物','74')
insert into RowTest values ('小生','物理','75')
insert into RowTest values ('小生','化学','76')
3:设计想法
行转列的原理就是把行的类别找出来当做查询的字段,利用case when 把当前的分数加到当前的字段上去,最后用group by 把数据整合在一起
4:通用方法
复制代码 代码如下:
declare @sql nvarchar(max)
set @sql='select Name'
select @sql=@sql+','+'isnull(max( case when Course='''+TCourse.Course+''' then Record end ),0)'+TCourse.Course
from (select distinct Course from RowTest)TCourse
set @sql=@sql+' from RowTest group by Name order by Name'
print @sql
exec(@sql)
说明: 把所有的课程名称取出来作为列(查询表TCourse)
用case when 的方法把sql 拼出来
5:课外试验
(1)加入数据
复制代码 代码如下:
insert into dbo.RowTest values ('小生','生物','110')
去除max 方法会报错,因为一条可能对应多行数据
(2)加入数据
复制代码 代码如下:
insert into dbo.RowTest values ('小生','算C','110')
数据会多出一列,但是其他人无此课程就会为0
至此,数据行转列ok
(二)列转行的新方法开始了
实现目的
1:实现原理
在网上看了别人的做法,基本都是用union all 来一个个转换的,我觉得不太好用。
首先我想到了要把所有的列名取出来,就在网上查了下获取表的所有列名
然后我可以把主表和列名形成的表串起来,这样就可以形成需要的列数,然后根据判断取值就完成了了,呵呵
2:建立表格
复制代码 代码如下:
create table CoulumTest
(
Name nvarchar(10),
语文 int,
数学 int,
英语 int
)
3:加入数据
复制代码 代码如下:
insert into CoulumTest values(N'张三',90,91,92)
insert into CoulumTest values(N'李四',80,81,82)
4:经典的地方来了
复制代码 代码如下:
select CT.Name,Col.name 课程,
(case when Col.name=N'语文' then CT.语文 when Col.name=N'数学' then CT.数学
when Col.name=N'英语' then CT.英语 end ) as 分数 from CoulumTest CT
left join (select name from SysColumns Where id=Object_Id('CoulumTest')) Col on Col.name<>'Name'
你没看错,一句话搞定,但是有个问题迷惑了我,我觉得还不够简化,如果可以把case when 都不用了就更好了,请大神们指点小弟一下了。怎么根据
Col的name 直接取得分数
123 在
Clash for Windows作者删库跑路了,github已404中评论 按理说只要你在国内,所有的流量进出都在监控范围内,不管你怎么隐藏也没用,想搞你分..原梓番博客 在
在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了,今天刚看,已经添加。..博主 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了,可以看看近期评论的其他文章..1111 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开,博主百忙中能否发个APP下载链接,佛跳墙或极光..路人 在
php中使用hyperf框架调用讯飞星火大模型实现国内版chatgpt功能示例中评论 教程很详细,如果加个前端chatgpt对话页面就完美了..Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号