SQL Server 行转列相关内容

• 主要为动态SQL和静态SQL 的写法（一列转多行)

表

新建表

if exists (select * from sysobjects where id = OBJECT_ID(N'[tbs]') and OBJECTPROPERTY(id,N'IsUserTable') =1)
drop table tbs;
-- 如果tbs存在，就删掉tbs
-- 创建 tbs
Create table tbs(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tbs values('张三' , '语文' , 76)
insert into tbs values('张三' , '数学' , 85)
insert into tbs values('张三' , '英语' , 76)
insert into tbs values('张三' , '化学' , 85)
insert into tbs values('张三' , '生物' , 79)
insert into tbs values('张三' , '地理' , 88)
insert into tbs values('张三' , '政治' , 90)
insert into tbs values('张三' , '物理' , 98)
insert into tbs values('李四' , '语文' , 89)
insert into tbs values('李四' , '数学' , 78)
insert into tbs values('李四' , '英语' , 98)
insert into tbs values('李四' , '化学' , 78)
insert into tbs values('李四' , '生物' , 67)
insert into tbs values('李四' , '地理' , 85)
insert into tbs values('李四' , '政治' , 97)
insert into tbs values('李四' , '物理' , 49)
insert into tbs values('王五' , '语文' , 86)
insert into tbs values('王五' , '数学' , 78)
insert into tbs values('王五' , '英语' , 93)
insert into tbs values('王五' , '化学' , 97)
insert into tbs values('王五' , '生物' , 97)
insert into tbs values('王五' , '地理' , 86)
insert into tbs values('王五' , '政治' , 85)
insert into tbs values('王五' , '物理' , 93)
GO

命令已成功完成。

查询表

select * from tbs;

张三	语文	76
张三	数学	85
张三	英语	76
张三	化学	85
张三	生物	79
张三	地理	88
张三	政治	90
张三	物理	98
李四	语文	89
李四	数学	78
李四	英语	98
李四	化学	78
李四	生物	67
李四	地理	85
李四	政治	97
李四	物理	49
王五	语文	86
王五	数学	78
王五	英语	93
王五	化学	97
王五	生物	97
王五	地理	86
王五	政治	85
王五	物理	93

行转列 静态SQL

-- ①行转列     静态SQL    SQL Server2000
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '英语' then 分数 else 0 end) 英语,
max(case 课程 when '化学' then 分数 else 0 end) 化学,
max(case 课程 when '生物' then 分数 else 0 end) 生物,
max(case 课程 when '地理' then 分数 else 0 end) 地理,
max(case 课程 when '政治' then 分数 else 0 end) 政治,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tbs
group by 姓名;

-- ②行转列   静态SQL    SQL Server2005
select * from (select * from tbs) a
pivot
(max(分数) for 课程 in (语文,数学,英语,化学,生物,地理,政治,物理)) b;

• 结果

姓名	语文	数学	英语	化学	生物	地理	政治	物理
李四	89	78	98	78	67	85	97	49
王五	86	78	93	97	97	86	85	93
张三	76	85	76	85	79	88	90	98

行转列 动态SQL

-- ③行转列  动态SQL   SQL SERVER 2000
declare @sql varchar(2000)
set @sql = 'select 姓名'
select @sql = @sql + ',max(case 课程 when '''+ 课程 + ''' then 分数 else 0 end) ['+ 课程 +'] '
from (select distinct 课程 from tbs) a
set @sql = @sql + 'from tbs group by 姓名'
exec(@sql);

-- ④行转列 动态SQL SQL SERVER 2005
declare @sql varchar(2000)
select @sql = isnull(@sql +'],[' ,'') +课程 from tbs group by 课程
set @sql = '[' + @sql +']'
exec('select * from (select * from tbs) a pivot(max(分数) for 课程 in ('+ @sql +'))b')

• 结果

姓名	地理	化学	生物	数学	物理	英语	语文	政治
李四	85	78	67	78	49	98	89	97
王五	86	97	97	78	93	93	86	85
张三	88	85	79	85	98	76	76	90