玩转-SQL2005数据库行列转换
时间:2024-01-23 23:28:02
注意:列转行的方法可能是我独创的了,呵呵,因为在网上找不到哦,全部是我自己写的,用到了系统的SysColumns
(一)行转列的方法
先说说行转列的方法,这个就比较好想了,利用拼sql和case when解决即可
实现目的
1:建立测试用的数据库
CREATE TABLE RowTest(
[Name] [nvarchar](10) NULL,--名稱
[Course] [nvarchar](10) NULL,--課程名稱
[Record] [int] NULL--課程的分數
)
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 ('小生','計算機','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 直接取得分数
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python实现k-means算法
![](https://img.aspxhome.com/file/2023/8/79588_0s.png)
MySQL主从复制配置心跳功能介绍
Django基础CBV装饰器和中间件的应用示例
![](https://img.aspxhome.com/file/2023/2/78832_0s.jpg)
Python参数传递实现过程及原理详解
在ASP.NET 2.0中操作数据之五十五:编辑和删除现有的二进制数据
![](https://img.aspxhome.com/file/2023/3/77173_0s.gif)
Python远程SSH库Paramiko详细操作
社区版pycharm创建django项目的方法(pycharm的newproject左侧没有项目选项)
![](https://img.aspxhome.com/file/2023/2/99682_0s.png)
9个Web设计中常见的可用性错误[译]
![](https://img.aspxhome.com/file/UploadPic/20093/11/01-10s.jpg)
JavaScript 作用域scope简单汇总
原生JS实现非常好看的计数器
![](https://img.aspxhome.com/file/2023/5/135965_0s.gif)
JavaScript判断各种浏览器类型及版本
Python使用lxml模块和Requests模块抓取HTML页面的教程
AlternatingItemTemplate类似于 ItemTemplate 元素
django框架自定义模板标签(template tag)操作示例
php+ajax+h5实现图片上传功能
sqlserver 千万数量级分页存储过程代码
抛砖引玉Web2.0设计指南
![](https://img.aspxhome.com/file/UploadPic/up/2008063013584749.gif)