SQL2005 行列转换（巩固篇）

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

--  Author: htl258(Tony)

--  SuBject: SQL2005行列互转整理

--  Date  : 2009-08-20 21:00:00

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

 

--1.行转列

 

If not object_id('[tb]') is null

 Drop table [tb]

Go

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

Insert tb

Select '张三','语文',60 union all

Select '张三','数学',70 union all

Select '张三','英语',80 union all

Select '张三','物理',90 union all

Select '李四','语文',65 union all

Select '李四','数学',75 union all

Select '李四','英语',85 union all

Select '李四','物理',95

go

 

--静态查询:

select * from tb pivot(max(分数) for 课程 in(语文,数学,英语,物理)) b

--动态查询:

declare @s varchar(max)

select @s=isnull(@s+',','')+课程 from tb group by 课程

select @s='select * from tb pivot(max(分数) for 课程in('+@s+'))b'

exec(@s)

/*

姓名        语文         数学         英语         物理

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

李四        65          75          85          95

张三        60          70          80          90

 

(2 行受影响)

*/

 

--加上总分静态查询

select 姓名,语文,数学,英语,物理,总分

from (select *,总分=sum(分数)over(partition by 姓名) from tb) a

pivot(max(分数) for 课程 in(语文,数学,英语,物理))b

 

--加上总分动态查询

declare @s varchar(max)

select @s=isnull(@s+',','')+课程 from tb group by 课程

select @s='

select 姓名,'+@s+' from

(select *,总分=sum(分数)over(partition by 姓名) from tb) a

pivot(max(分数) for 课程in('+@s+'))b'

exec(@s)

/*

姓名        语文         数学         英语         物理         总分

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

张三        60          70          80          90          300

李四        65          75          85          95          320

 

(2 行受影响)

*/

 

--2.列转行

 

If not object_id('tb') is null

    Drop table tb

Go

Create table tb(姓名 nvarchar(2),[语文] int,[数学] int,[英语] int,[物理] int)

Insert tb

Select '张三',60,70,80,90 union all

Select '李四',65,75,85,95

Go

--静态查询

select * from tb unpivot(分数 for 课程 in(语文,数学,英语,物理))b

--动态查询

declare @s varchar(max)

select @s=isnull(@s+',','')+name from syscolumns where id=object_id('tb') and name not in('姓名') order by colid

select @s='select * from tb unpivot(分数for 课程in('+@s+'))b'

exec(@s)

/*

姓名  分数 课程

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

李四65 语文

李四75 数学

李四85 英语

李四95 物理

张三60 语文

张三70 数学

张三80 英语

张三90 物理

 

(8 行受影响)

*/