行列转置

Question

大家好，我初涉SQL Server，对行列转置很模糊。自己做了个例子如下：
create table STA
(
  Name varchar(20)
 ,Class varchar(100)
 ,Major varchar(30)
)
insert into STA values ('张三','3班','计算机')
insert into STA values ('李四','3班','计算机')
insert into STA values ('王五','3班','计算机')
insert into STA values ('贾六','2班','自动化')
insert into STA values ('赵七','2班','自动化')
insert into STA values ('崔九','1班','电子信息')
insert into STA values ('刘石','1班','自动化')
insert into STA values ('司马','4班','电子信息')
insert into STA values ('尉迟','4班','电子信息')
go然后进行了执行了以下操作：
select Major,
      MAX(case Class when '1班' then Name else null end) '1班',
      MAX(case Class when '2班' then Name else null end) '2班',
      MAX(case Class when '3班' then Name else null end) '3班',
      MAX(case Class when '4班' then Name else null end) '4班'
from STA group by Major生成的结果是：
 major   1班       2班      3班      4班
电子信息 崔九 NULL NULL 尉迟
计算机 NULL NULL 张三 NULL
自动化 刘石 赵七 NULL NULL问题出现了：比如  自动化2班有两人--赵七 和 贾六  ；电子信息4班有两人--尉迟 和 尉迟 
 可见查询的结果并非我想得到的，请教各位前辈，该怎么解决？  帮忙写个完整的最好啦 ，谢谢啦 ！

Answer 1

想如下显示，是否可行呢？major   1班      2班            3班                4班
电子信息 崔九    NULL         NULL            尉迟,司马
计算机   NULL   NULL       张三,李四,王五        NULL
自动化   刘石   赵七,贾六      NULL              NULL

Answer 2

看此文第4点./*
标题：普通行列转换(version 3.0)
作者：爱新觉罗.毓华(十八年风雨，守得冰山雪莲花开)
时间：2010-05-07
地点：重庆航天职业学院
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，(version 2.0)增加sql server 2005的有关写法，(version 3.0)增加分数统计及项目合并等内容。
问题：假设有张学生成绩表(tb)如下:(相关字段说明：name-->姓名,course-->课程名称,type-->考试类型或次数,score-->成绩)
name course type score       
---- ------ ---- -----
李四 数学   1    81
李四 数学   2    82
李四 物理   1    83
李四 物理   2    84
李四 语文   1    85
李四 语文   2    86
张三 数学   1    91
张三 数学   2    92
张三 物理   1    93
张三 物理   2    94
张三 语文   1    95
张三 语文   2    96
*/
--创建测试数据
create table tb(name nvarchar(10),course nvarchar(10),type int,score int)
insert into tb values(N'李四',N'数学',1,81)
insert into tb values(N'李四',N'数学',2,82)
insert into tb values(N'李四',N'物理',1,83)
insert into tb values(N'李四',N'物理',2,84)
insert into tb values(N'李四',N'语文',1,85)
insert into tb values(N'李四',N'语文',2,86)
insert into tb values(N'张三',N'数学',1,91)
insert into tb values(N'张三',N'数学',2,92)
insert into tb values(N'张三',N'物理',1,93)
insert into tb values(N'张三',N'物理',2,94)
insert into tb values(N'张三',N'语文',1,95)
insert into tb values(N'张三',N'语文',2,96)
go
/*
1、需要如下结果(对每个人每门课程每次的成绩进行横向排列)
name course type_1 type_2      
---- ------ ------ ------
李四 数学   81     82
李四 物理   83     84
李四 语文   85     86
张三 数学   91     92
张三 物理   93     94
张三 语文   95     96
*/
--1.1 SQL SERVER 2000 静态SQL，指考试类型或次数type固定为1或2。(以下同)
select name,course, 
  max(case type when 1 then score else 0 end) type_1,
  max(case type when 2 then score else 0 end) type_2
from tb
group by name,course
order by name,course
--1.2 SQL SERVER 2000 动态SQL，指考试类型或次数type值不确定，不是固定为1或2。(以下同)
declare @sql varchar(8000)
set @sql = 'select name,course'
select @sql = @sql + ',max(case type when '''+ltrim(type)+''' then score else 0 end) [type_'+ltrim(type)+']'
from (select distinct type from tb) as a
set @sql = @sql + ' from tb group by name,course'
exec(@sql)
--1.3 SQL SERVER 2005 静态SQL。
select name,course,[1] type_1,[2] type_2 from (select * from tb) a pivot (max(score) for type in ([1],[2])) b order by name,course
--1.4 SQL SERVER 2005 动态SQL。
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
select @sql1=isnull(@sql1+'],[','')+ltrim(type) from tb group by type
set @sql1 = '['+@sql1+']'
select @sql2 = isnull(@sql2+'],[','')+ltrim(type)+'] [type_'+ltrim(type) from tb group by type
set @sql2 = 'select name,course,['+@sql2+']'
exec(@sql2 + ' from (select * from tb) a pivot (max(score) for type in ('+@sql1+')) b order by name,course')/*
2、需要如下结果(对每个人每门课程每次的成绩，总分，平均分，最高分，最低分进行横向排列)
name course type_1 type_2 sum avg   max min
---- ------ ------ ------ --- ----- --- ---
李四 数学   81     82     163 81.50 82  81
李四 物理   83     84     167 83.50 84  83
李四 语文   85     86     171 85.50 86  85
张三 数学   91     92     183 91.50 92  91
张三 物理   93     94     187 93.50 94  93
张三 语文   95     96     191 95.50 96  95
*/
--2.1 SQL SERVER 2000 静态SQL。
select name,course,
  max(case type when 1 then score else 0 end) type_1,
  max(case type when 2 then score else 0 end) type_2,
  sum(score) [sum],
  cast(avg(score*1.0) as decimal(18,2)) [avg],
  max(score) [max],
  min(score) [min]
from tb
group by name,course
order by name,course
--2.2 SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select name,course'
select @sql = @sql + ',max(case type when '''+ltrim(type)+''' then score else 0 end) [type_'+ltrim(type)+']'
from (select distinct type from tb) as a
set @sql = @sql + ',sum(score) [sum],cast(avg(score*1.0) as decimal(18,2)) [avg],max(score) [max],min(score) [min] from tb group by name,course'
exec(@sql) 
--2.3 SQL SERVER 2005 静态SQL。
select m.*,n.[sum],n.[avg],n.[max],n.[min] from
(select name,course,[1] type_1,[2] type_2 from (select * from tb) a pivot (max(score) for type in ([1],[2])) b) m,
(select name,course,sum(score) [sum],cast(avg(score*1.0) as decimal(18,2)) [avg],max(score) [max],min(score) [min] from tb group by name,course) n
where m.name=n.name and m.course=n.course
order by m.name,m.course
--2.4 SQL SERVER 2005 动态SQL。
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
select @sql1=isnull(@sql1+'],[','')+ltrim(type) from tb group by type
set @sql1='['+@sql1+']'
select @sql2=isnull(@sql2+'],[','')+ltrim(type)+'] [type_'+ltrim(type) from tb group by type
set @sql2='select m.*,n.[sum],n.[avg],n.[max],n.[min] from (select name,course,['+@sql2+']'
exec(@sql2+' from (select * from tb) a pivot (max(score) for type in ('+@sql1+')) b) m , 
(select name,course,sum(score) [sum],cast(avg(score*1.0) as decimal(18,2)) [avg],max(score) [max],min(score) [min] from tb group by name,course) n
where m.name=n.name and m.course=n.course 
order by name,course')/*
3、需要如下结果(对每个人每门课程的成绩进行统计再按照课程进行横向排列)
姓名 数学合计 物理合计 语文合计
---- -------- -------- --------
李四 163      167      171
张三 183      187      191
*/
--3.1 SQL SERVER 2000 静态SQL。
select name 姓名,
  sum(case course when '数学' then score else 0 end) [数学合计],
  sum(case course when '物理' then score else 0 end) [物理合计],
  sum(case course when '语文' then score else 0 end) [语文合计]
from tb
group by name 
order by name
--3.2 SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql='select name 姓名'
select @sql=@sql+',sum(case course when '''+course+''' then score else 0 end) ['+course+'合计]'
from (select distinct course from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
--3.3 SQL SERVER 2005 静态SQL。
select name [姓名],[数学] [数学合计],[物理] [物理合计],[语文] [语文合计] from (select name,course,score from tb) a pivot (sum(score) for course in ([数学],[物理],[语文])) b order by name
--3.4 SQL SERVER 2005 动态SQL。
declare @sql1 nvarchar(4000)
declare @sql2 nvarchar(4000)
select @sql1=isnull(@sql1+'],[','')+course from tb group by course
set @sql1='['+@sql1+']'
select @sql2=isnull(@sql2+'],[','')+course+'] ['+course+N'合计' from tb group by course
set @sql2 = 'select name '+N'[姓名'+'],['+@sql2+']'
exec(@sql2 + ' from (select name,course,score from tb) a pivot (sum(score) for course in ('+@sql1+')) b order by name')/*
4、需要如下结果(对每个人每门课程的成绩进行合并后再按照课程进行横向排列)
姓名 数学组合 物理组合 语文组合
---- -------- -------- --------
张三 91,92    93,94    95,96
李四 81,82    83,84    85,86
*/
--4.1 SQL SERVER 2000 静态SQL。需要使用函数先合并数据再进行行列转换。
--自定义函数实现字符串合并问题。
go
create function dbo.f_str(@name nvarchar(10),@course nvarchar(10)) returns varchar(100)
as
begin
  declare @str varchar(1000)
  set @str=''
  select @str=@str+','+cast(score as varchar) from tb where name=@name and course=@course
  set @str=right(@str,len(@str)-1)
  return @str
end
go
--实现行列转换
select name 姓名,
  max(case course when '数学' then score else '' end) [数学组合],
  max(case course when '物理' then score else '' end) [物理组合],
  max(case course when '语文' then score else '' end) [语文组合]
from
(
  --调用函数
  select name,course,score=dbo.f_str(name,course) from tb group by name,course
) t
group by name
order by name
drop function dbo.f_str
--4.2 SQL SERVER 2000 动态SQL。仍然需要使用上述字符串合并的函数。
declare @sql varchar(8000)
set @sql='select name 姓名'
select @sql=@sql+',max(case course when '''+course+''' then score else '''' end) ['+course+'组合]'
from (select distinct course from tb) as a
set @sql=@sql+' from (select name,course,score=dbo.f_str(name,course) from tb group by name,course) t group by name order by name'
exec(@sql)
--4.3 SQL SERVER 2005 静态SQL。
select name 姓名,
  [数学组合]=stuff((select ','+ltrim(score) from tb where name=t.name and course=N'数学' for xml path('')),1,1,''),
  [物理组合]=stuff((select ','+ltrim(score) from tb where name=t.name and course=N'物理' for xml path('')),1,1,''),
  [语文组合]=stuff((select ','+ltrim(score) from tb where name=t.name and course=N'语文' for xml path('')),1,1,'')
from tb t
group by name
--4.4 SQL SERVER 2005 动态SQL。
declare @sql nvarchar(4000)
set @sql = 'select name '+N'[姓名]'
select @sql = @sql + ',stuff((select '',''+ltrim(score) from tb where name=t.name and course=N'''+course+''' for xml path('''')),1,1,'''') ['+course+N'组合]'
from (select distinct course from tb) as a
set @sql = @sql+' from tb t group by name order by name'
exec(@sql)drop table tb/*
针对上面的行列转换进行逆向实现。表原始数据如下：
name type 数学 物理 语文          
---- ---- ---- ---- ----
李四 1    81   83   85
李四 2    82   84   86
张三 1    91   93   95
张三 2    92   94   96
*/--创建测试数据
create table tb(name nvarchar(10),type int,数学 int,物理 int,语文 int)
insert into tb values(N'李四',1,81,83,85)
insert into tb values(N'李四',2,82,84,86)
insert into tb values(N'张三',1,91,93,95)
insert into tb values(N'张三',2,92,94,96)
go/*
5.想要的结果如下：
name course type score       
---- ------ ---- -----
李四 数学   1    81
李四 数学   2    82
李四 物理   1    83
李四 物理   2    84
李四 语文   1    85
李四 语文   2    86
张三 数学   1    91
张三 数学   2    92
张三 物理   1    93
张三 物理   2    94
张三 语文   1    95
张三 语文   2    96
*/--5.1 SQL SERVER 2000 静态SQL
select name,course='数学',type,score=数学 from tb
union all
select name,course='物理',type,score=物理 from tb
union all
select name,course='语文',type,score=语文 from tb
order by name,course,type
--5.2 SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select name,[course]='+quotename(Name,'''')+',type,[score]='+quotename(Name)+' from tb'
from syscolumns 
where name!=N'name' and name!=N'type' and ID=object_id('tb')--表名tb，不包含列名为name和type的其它列
order by colid asc
exec(@sql+' order by name,course,type')
--5.3 SQL SERVER 2005 静态SQL。
select name,course,type,score from tb unpivot (score for course in([数学],[物理],[语文])) t order by name,course,type
--5.4 SQL SERVER 2005 动态SQL。
declare @sql nvarchar(4000)
select @sql=isnull(@sql+' union all ' , '')+' select name,[course]=N'+quotename(Name,'''')+',type,[score]='+quotename(Name)+' from tb'
from syscolumns
where name!=N'name' and name!=N'type' and ID=object_id('tb')
order by colid asc
exec(@sql+' order by name,course,type')

Answer 3

显示的很不好，弄个表格不好弄，不知道这样效果会不会好点
major   |1班   |2班        |  3班                 | 4班
电子信息 |崔九  |NULL      |  NULL              |尉迟,司马
计算机   |NULL |NULL       |  张三,李四,王五     | NULL
自动化   |刘石  |赵七,贾六   | NULL              |NULL

Answer 4

if object_id('STA') is not null drop table STA
go
create table STA
(
  Name varchar(20)
 ,Class varchar(100)
 ,Major varchar(30)
)
insert into STA values ('张三','3班','计算机')
insert into STA values ('李四','3班','计算机')
insert into STA values ('王五','3班','计算机')
insert into STA values ('贾六','2班','自动化')
insert into STA values ('赵七','2班','自动化')
insert into STA values ('崔九','1班','电子信息')
insert into STA values ('刘石','1班','自动化')
insert into STA values ('司马','4班','电子信息')
insert into STA values ('尉迟','4班','电子信息')
goselect Major,
  MAX(case Class when '1班' then Name else null end) '1班',
  MAX(case Class when '2班' then Name else null end) '2班',
  MAX(case Class when '3班' then Name else null end) '3班',
  MAX(case Class when '4班' then Name else null end) '4班'
from (select Name=stuff((select ','+rtrim(Name) from STA where Class=t.Class and Major=t.Major for xml path('')),1,1,''),Class,Major from STA t) as tt
group by Major
/*
Major 1班 2班 3班 4班
--------------------------------------------------------------
电子信息 崔九 NULL NULL 司马,尉迟
计算机 NULL NULL 张三,李四,王五 NULL
自动化 刘石 贾六,赵七 NULL NULL
*/

Answer 5

非常感谢大家的积极回复。3楼前辈让我了解了各种情况下的相关SQL，正在学习中
着实感谢5楼前辈，仅此
"from (select Name=stuff((select ','+rtrim(Name) from STA where Class=t.Class and Major=t.Major for xml path('')),1,1,''),Class,Major from STA t) as tt"
一句解决了问题，我还得好好理解学习这句SQL 。

Answer 6

顶5楼，好思路！
/*if object_id('X') is not null drop table X
go
create table X
(
  Name varchar(20)
 ,Class varchar(100)
 ,Major varchar(30)
)
insert into X values ('张三','3班','计算机')
insert into X values ('李四','3班','计算机')
insert into X values ('王五','3班','计算机')
insert into X values ('贾六','2班','自动化')
insert into X values ('赵七','2班','自动化')
insert into X values ('崔九','1班','电子信息')
insert into X values ('刘石','1班','自动化')
insert into X values ('司马','4班','电子信息')
insert into X values ('尉迟','4班','电子信息')
*/
--先做成这样的
select class,major,name=stuff((select ',' + name from X where X.class=t.class and X.major=t.major for xml path('')),1,1,'') from X t group by class,major
/*
1班 电子信息 崔九
1班 自动化 刘石
2班 自动化 贾六,赵七
3班 计算机 张三,李四,王五
4班 电子信息 司马,尉迟
*/
--再转
;with t as
(select class,major,name=stuff((select ',' + name from X where X.class=t.class and X.major=t.major for xml path('')),1,1,'') from X t group by class,major)
select major,
max(case when class='1班' then name else null end) as '1班',
max(case when class='2班' then name else null end) as '2班',
max(case when class='3班' then name else null end) as '3班',
max(case when class='4班' then name else null end) as '4班'
from t group by major

Answer 7

虽然结贴了，但是现在有个新需求。如果是动态的插入或删除数据，需要使用动态SQL,但是我写的动态SQL总是有问题。这次没法给分了，但还是希望大家提供一下帮助，非常感谢！

Answer 8

--琢磨出来了，这样应该是对的。
---动态SQL
declare @sql varchar(8000)
set @sql ='select Major'
select  @sql =@sql+',max(case Class when '''+Class+''' then Name else null end)'+''''+Class+'''' 
from (select distinct Class from STA) as a 
set @sql=@sql 
+' from (select Name=stuff((select '',''+rtrim(Name) from STA where Class=t.Class and Major=t.Major for xml path('''')),1,1,''''),Class,Major from STA t) as tt 
group by Major'
print @Name
print @sql
exec (@sql)