（100）分解决Sql Server 中表中信息转化成行表头信息和列表头信息问题？急！！

MS-SQL Server > 应用实例
收藏 回复
[问题点数：100分，结帖人boywangliang]
更多帖子
私信 空间 博客
boywangliang
本版专家分：5
结帖率 81.25%
题目信息：

A表信息：
A_001 上午
A_002 下午


B表信息：
B_001 星期一
B_002 星期二
B_003 星期三
B_004 星期四
B_005 星期五
B_006 星期六
B_007 星期日

C表信息：
C_001 A_001 B_001 张三
C_002 A_001 B_002 李四
C_003 A_001 B_003 王五
C_004 A_001 B_004 赵六
C_005 A_001 B_005 吕七
C_006 A_001 B_006 刘长
C_007 A_001 B_007 赵云
C_008 A_002 B_001 诸葛亮
C_009 A_002 B_002 马云
C_010 A_002 B_003 刘备
C_011 A_002 B_004 程咬金
C_012 A_002 B_005 李卫
C_013 A_002 B_006 周瑜
C_014 A_002 B_007 小桥


实现效果：
星期一 星期二 星期三 星期四 星期五 星期六 星期日
上午 张三 李四 王五 赵六 吕七 刘长 赵云
下午 诸葛亮 马云 刘备 程咬金 李卫 周瑜 小桥


注意事项：行表头和列表头都是从表中读取，要动态实现。
只看楼主 引用 举报 楼主
更多帖子
私信 空间 博客
Andy__Huang
本版专家分：67713
Blank
铜牌 2013年9月 总版技术专家分月排行榜第三
Blank
红花 2013年7月 MS-SQL Server大版内专家分月排行榜第一
2009年3月 Oracle大版内专家分月排行榜第一
2009年2月 Oracle大版内专家分月排行榜第一
Blank
黄花 2013年9月 MS-SQL Server大版内专家分月排行榜第二
2013年8月 MS-SQL Server大版内专家分月排行榜第二
2009年4月 Oracle大版内专家分月排行榜第二
2009年1月 Oracle大版内专家分月排行榜第二
2004年10月 MS-SQL Server大版内专家分月排行榜第二
Blank
蓝花 2013年10月 MS-SQL Server大版内专家分月排行榜第三
2008年12月 Oracle大版内专家分月排行榜第三
2004年11月 MS-SQL Server大版内专家分月排行榜第三
搜索一下以前帖子，解决这类问题有大把多帖子
只看TA 引用 举报 #1得分 0
更多帖子
私信 空间 博客
showit
本版专家分：0
关注！！！！
只看TA 引用 举报 #2得分 0
更多帖子
私信 空间 博客
fpzgm
本版专家分：7597
/*

标题：普通行列转换(version 2.0)

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)

时间：2008-03-09

地点：广东深圳

说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加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 课程

set @sql = '[' + @sql + ']'

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 + ',' , '') + '['+ltrim(课程名称)+']' 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   





ALTER PROCEDURE [dbo].[pro_score]



AS

declare @xq varchar(20)

declare @zy varchar(20)

declare @sql varchar(1000)



select @sql = isnull(@sql + ',' , '') + '['+ltrim(课程名称)+']' from score_view group by 课程名称



exec ('select m.*, n.平均分,n.总分 from (select * from (select 学号,姓名,学期,专业,课程名称,成绩 from score_view   ) a

pivot (max(成绩) for 课程名称 in ('+@sql+') )  b ) m ,

(select 学号, 学期,专业,cast(avg(成绩*1.0) as decimal(18,2))平均分,sum(成绩)总分 from score_view  group by 学号,学期,专业 having 学期=''20081'' and 专业=''软件技术'') n 

where m.学号 = n.学号 ')



--20081   -- @xq





------------------

------------------
只看TA 引用 举报 #3得分 30
更多帖子
私信 空间 博客
dawugui
本版专家分：440462
Blank
名人 2019年 荣获名人称号
年度总版至少三次排名前十即授予名人勋章
Blank
榜眼 2008年 总版技术专家分年内排行榜第二
Blank
进士 2010年 总版技术专家分年内排行榜第四
2009年 总版技术专家分年内排行榜第五
2007年 总版技术专家分年内排行榜第六
Blank
金牌 2009年3月 总版技术专家分月排行榜第一
2008年12月 总版技术专家分月排行榜第一
2008年3月 总版技术专家分月排行榜第一
2007年12月 总版技术专家分月排行榜第一
2007年10月 总版技术专家分月排行榜第一
行列转换吧,参考楼上转我的那个帖.
只看TA 引用 举报 #4得分 0
更多帖子
私信 空间 博客
dawugui
本版专家分：440462
Blank
名人 2019年 荣获名人称号
年度总版至少三次排名前十即授予名人勋章
Blank
榜眼 2008年 总版技术专家分年内排行榜第二
Blank
进士 2010年 总版技术专家分年内排行榜第四
2009年 总版技术专家分年内排行榜第五
2007年 总版技术专家分年内排行榜第六
Blank
金牌 2009年3月 总版技术专家分月排行榜第一
2008年12月 总版技术专家分月排行榜第一
2008年3月 总版技术专家分月排行榜第一
2007年12月 总版技术专家分月排行榜第一
2007年10月 总版技术专家分月排行榜第一
如果看不懂的话,晚上有时间帮你做一下.
只看TA 引用 举报 #5得分 0
更多帖子
私信 空间 博客
dawugui
本版专家分：440462
Blank
名人 2019年 荣获名人称号
年度总版至少三次排名前十即授予名人勋章
Blank
榜眼 2008年 总版技术专家分年内排行榜第二
Blank
进士 2010年 总版技术专家分年内排行榜第四
2009年 总版技术专家分年内排行榜第五
2007年 总版技术专家分年内排行榜第六
Blank
金牌 2009年3月 总版技术专家分月排行榜第一
2008年12月 总版技术专家分月排行榜第一
2008年3月 总版技术专家分月排行榜第一
2007年12月 总版技术专家分月排行榜第一
2007年10月 总版技术专家分月排行榜第一
--以下为sql 2000中的写法,有关2005的写法参考3楼的帖.
create table A(a1 varchar(10),a2 varchar(10))

insert into a values('A_001', '上午') 

insert into a values('A_002', '下午') 

create table B(b1 varchar(10),b2 varchar(10))

insert into b values('B_001', '星期一') 

insert into b values('B_002', '星期二') 

insert into b values('B_003', '星期三')

insert into b values('B_004', '星期四') 

insert into b values('B_005', '星期五') 

insert into b values('B_006', '星期六') 

insert into b values('B_007', '星期日') 

create table c(c1 varchar(10) , c2 varchar(10) , c3 varchar(10) , c4 varchar(10))

insert into c values('C_001', 'A_001', 'B_001', '张三')

insert into c values('C_002', 'A_001', 'B_002', '李四')

insert into c values('C_003', 'A_001', 'B_003', '王五')

insert into c values('C_004', 'A_001', 'B_004', '赵六')

insert into c values('C_005', 'A_001', 'B_005', '吕七')

insert into c values('C_006', 'A_001', 'B_006', '刘长')

insert into c values('C_007', 'A_001', 'B_007', '赵云')

insert into c values('C_008', 'A_002', 'B_001', '诸葛亮')

insert into c values('C_009', 'A_002', 'B_002', '马云')

insert into c values('C_010', 'A_002', 'B_003', '刘备')

insert into c values('C_011', 'A_002', 'B_004', '程咬金')

insert into c values('C_012', 'A_002', 'B_005', '李卫')

insert into c values('C_013', 'A_002', 'B_006', '周瑜')

insert into c values('C_014', 'A_002', 'B_007', '小桥')

go



--sql 2000静态,指B表的B2字段固定为星期一到星期日.

select a.a2 , 

       max(case b.b2 when '星期一' then c.c4 else '' end) [星期一],

       max(case b.b2 when '星期二' then c.c4 else '' end) [星期二],

       max(case b.b2 when '星期三' then c.c4 else '' end) [星期三],

       max(case b.b2 when '星期四' then c.c4 else '' end) [星期四],

       max(case b.b2 when '星期五' then c.c4 else '' end) [星期五],

       max(case b.b2 when '星期六' then c.c4 else '' end) [星期六],

       max(case b.b2 when '星期日' then c.c4 else '' end) [星期日]

from a , b , c

where a.a1 = c.c2 and c.c3 = b.b1

group by a.a2

/*

a2         星期一        星期二        星期三        星期四        星期五        星期六        星期日        

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 

上午         张三         李四         王五         赵六         吕七         刘长         赵云

下午         诸葛亮        马云         刘备         程咬金        李卫         周瑜         小桥



（所影响的行数为 2 行）

*/



--sql 2000动态,指B表的B2字段不固定.

declare @sql varchar(8000)

set @sql = 'select a.a2 '

select @sql = @sql + ' , max(case b.b2 when ''' + b2 + ''' then c4 else '''' end) [' + b2 + ']'

from (select b2 from b) as t

set @sql = @sql + ' from a , b , c where a.a1 = c.c2 and c.c3 = b.b1 group by a.a2' 

exec(@sql) 



/*

a2         星期一        星期二        星期三        星期四        星期五        星期六        星期日        

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 

上午         张三         李四         王五         赵六         吕七         刘长         赵云

下午         诸葛亮        马云         刘备         程咬金        李卫         周瑜         小桥

*/



drop table a , b , c
只看TA 引用 举报 #6得分 70
更多帖子
私信 空间 博客
feng442624978
本版专家分：2
select * from 
(select A.a2,B.b2,C.c4 from A,B,C where A.a1=C.c2 and B.b1=C.C3) A
pivot 
(
max(c4)
for b2 in 
(
[星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]
)
) piv
只看TA 引用 举报 #7得分 0
更多帖子
私信 空间 博客
feng442624978
本版专家分：2
如果不明白 上面语句的意思 请看我 博客....
给分走人 谢谢......
只看TA 引用 举报 #8得分 0
更多帖子
私信 空间 博客
abuying
本版专家分：10385




--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + '],[' , '') + b2 from b group by b2

set @sql = '[' + @sql + ']'

exec ('select * from (select *  from a,b,c where a.a1 = c.c2 and c.c3 = b.b1) N pivot (max(c4) for 课程 in (' + @sql + ')) M')
只看TA 引用 举报 #9得分 0
收藏 回复
boywangliang

等级：

关注 私信 TA的帖子
将sqlserver表中指定数据转化为insert语句

将sqlserver表中指定数据转化为insert语句 部分数据类型固定不出现，自增列不出现

SQL导出EXCEL(带表头)的存储过程

SQL导出EXCEL(带表头)的存储过程

sql server系统表详细说明

sql server系统表详细说明

用SQL语句j查SQLServer的表结构

用SQL语句j查SQLServer的表结构 用SQL语句j查SQLServer的表结构

SQL Server中各个系统表的作用

SQL Server中各个系统表的作用

sql server 练习表

此文档为sql server练习表，适合初学者练习用，数据可自行增加

SQLServer中临时表与表变量的区别

资源名称：SQLServer中临时表与表变量的区别内容简介： 本文档主要讲述的是SQLServer中临时表与表变量的区别;希望本文档会给有需要的朋友带来帮助;感兴趣的朋友可以过来看看。资源截图： 资源太大，传百度网盘了，...

SqlServer数据库中数据表的创建者非DBO的解决方案

SqlServer数据库中数据表的创建者非DBO的解决方案

sqlserver 支持多表联合查询分页存储过程

sqlserver 支持多表联合查询分页存储过程 sqlserver 支持多表联合查询分页存储过程 sqlserver 支持多表联合查询分页存储过程 sqlserver 支持多表联合查询分页存储过程

sqlserver表数据插入到oracle表中的一种实现方式(表结构相同)

表结构的创建比较简单，但是表的数据量太大，一时也想不到怎么把sqlserver表数据复制到oracle中，于是请教公司主管，用存储过程实现可以查询出所有数据的insert脚本，在oracle库中创建好表，直接把sqlserver中的...

SqlServer删除所有表数据语句

用一条SqlServer语句即可删除当前数据库中所有表的数据，表的结构保持不变。

SQL Server查看所有表大小，所占空间

用SQL语句查看SQL Server中的数据库查看所有表大小，所占空间

sqlserver 批量创建表

使用sqlserver2008批量创建表，表名做为参数传入，批量向各个表中插入数据

sqlServer数据库大表分区方案

sqlServer数据库大表分区方案，新建表的时候。。如何表分区。

SQLserver查询表大小，重整index碎片

资源名称：SQLserver查询表大小，重整index碎片 资源截图： 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。

sql server触发器中自动生成的临时表

此文档中详细的记载了，sql server触发器中自动生成的临时表，希望可以帮到有需要的朋友们！

SQL Server数据库表跟踪简单方法

自己总结的比较简单的SQL server数据库表的跟踪方法，勿喷，谢谢

在一个打开的SQLServer表中动态增加字段

Delphi实例：运行时刻，在一个已经打开的SQLServer数据库表中动态地增加新字段。

3天掌握SQL Server 2012 数据库

SQL Server是由Microsoft开发和推广的关系数据库管理系统（DBMS），Microsoft SQL Server 2012 是一个全面的数据库平台，使用集成的商业智能 (BI)工具提供了企业级的数据管理。Microsoft SQL Server 数据库引擎为...

查看sqlserver表分区数据分布

查看sqlserver表分区数据分布，需要的同学下载看看吧

相关热词 c# 解析cad c#数字格式化万 c#int转byte c#格式化日期 c# wpf 表格 c# 实现ad域验证登录 c#心跳包机制 c#使用fmod.dll c#dll vb 调用 c# outlook