服务器之家

服务器之家 > 正文

玩转-SQL2005数据库行列转换

时间:2020-07-09 17:27     来源/作者:mssql教程网

注意:列转行的方法可能是我独创的了,呵呵,因为在网上找不到哦,全部是我自己写的,用到了系统的SysColumns

(一)行转列的方法

说说行转列的方法,这个就比较好想了,利用拼sql和case when解决即可

实现目的

玩转-SQL2005数据库行列转换

 

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

(二)列转行的新方法开始了

实现目的

玩转-SQL2005数据库行列转换

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 直接取得分数

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意 2019-07-07
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
返回顶部