正文
sqlserver横表转,行转列 sqlserver
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
MS SqlServer 2000 行转列 的问题
/*
sql server 2005的有关写法。
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 748393
李四 748494
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go
--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb
-- 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (项目 nvarchar(5),值 int,位置 int)
insert into #T
select '项目1',15,1 union all
select '项目1',34,2 union all
select '项目1',56,3 union all
select '项目1',42,4 union all
select '项目2',56,1 union all
select '项目2',67,2 union all
select '项目2',31,3 union all
select '项目2',89,4 union all
select '项目3',45,1 union all
select '项目3',22,2 union all
select '项目3',8,3 union all
select '项目3',23,4
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when 项目='''+项目+''' then 值 end) ['+项目+']'
from #T group by 项目
set @sql=stuff(@sql,1,1,'')
exec ('select '+@sql+',位置 from #T group by 位置')
/*
项目1 项目2 项目3 位置
----------- ----------- ----------- -----------
15 56 45 1
34 67 22 2
56 31 8 3
42 89 23 4
*/
/*
-- Author:Flystone
-- Version:V1.001 Date:2008-05-15 初稿
-- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常
-- 2、 增加了形如yyyy-mm-dd hh:mm:ss
-- yyyy-m-d h:m:s 格式的处理
*/
-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(xm varchar(10),v int,pos int)
Go
Insert into ta
select '项目1',15,1 union all
select '项目1',34,2 union all
select '项目1',56,3 union all
select '项目1',42,4 union all
select '项目2',56,1 union all
select '项目2',67,2 union all
select '项目2',31,3 union all
select '项目2',89,4 union all
select '项目3',45,1 union all
select '项目3',22,2 union all
select '项目3',8,3 union all
select '项目3',23,4
Go
--Start
declare @s varchar(1000)
select @s = isnull(@s +',','')+ '['+xm+']= max(case when xm = '''+xm+''' then v else 0 end)'
from (select distinct xm from ta) a
exec('select '+@s + ',pos from ta group by pos')
--Result:
/*
项目1 项目2 项目3 pos
----------- ----------- ----------- -----------
15 56 45 1
34 67 22 2
56 31 8 3
42 89 23 4
*/
--End
-- -- (Roy)生成测试数据
set nocount on;
if not object_id('Tempdb..#T') is null
drop table #T
Go
set nocount on;
Create table #T([项目] nvarchar(3),[值] int,[位置] int)
Insert #T
select N'项目1',15,1 union all
select N'项目1',34,2 union all
select N'项目1',56,3 union all
select N'项目1',42,4 union all
select N'项目2',56,1 union all
select N'项目2',67,2 union all
select N'项目2',31,3 union all
select N'项目2',89,4 union all
select N'项目3',45,1 union all
select N'项目3',22,2 union all
select N'项目3',8,3 union all
select N'项目3',23,4
Go
declare @s nvarchar(1000)
select @s=isnull(@s+',','')+quotename([项目]) from #T group by [项目]
exec('select '+@s+',[位置] from #T pivot (max([值]) for [项目] in('+@s+'))b')
--Result:
/*
项目1 项目2 项目3 位置
----------- ----------- ----------- -----------
15 56 45 1
34 67 22 2
56 31 8 3
42 89 23 4
*/
--End
相关问答
Q1: 请问Sql server如何把一个内连接的输出的表格横向显示
普通sql很难做，要用sqlserver存储过程，用游标循环shorename的表，然后用case when一个一个拼起来
比如游标里写
create table aa as select osid,osuser,ostime,sum(case when shopname=@shopname and then osnumber else 0 end) as @shopname
from aa, shopname where aa.osid=shopname.osid
这样表aa在循环中每次都加上一个新shopname字段，直到游标shopname循环完毕，思路就是这样，最后再写个delete语句，把aa表里osnumber=0删掉就可以了，因为它都是表连接时由于shopname不匹配产生的垃圾数据
Q2: sqlserver不同数据库间如何转移表、数据
用查询分析器在A库上执行
1.复制表：select * into b..tableA from tableA where 1 = 2
2复制表数据：insert into b..tableA select * from tableA
如果B中没有表TABLEA，直接复制表加数据select * into b..tableA from tableA
Q3: sqlserver 如何将竖列转化为横向
select groupfield1, groupfield2, max(case when type=1 then value end) col1,
max(case when type=2 then value end) col2
from table
group by groupfield1, groupdfield2
Q4: sqlserver用编程语句怎么把两个表格横向合成一个
在第一个表里面增加字段alter table a add b varchar(20)
然后把第二个表中内容插入就可以了 insert into table a (字段，字段，字段） select 字段，字段，字段 from table b
