SQL2005 行列转换（巩固篇）

原帖：http://blog.youkuaiyun.com/htl258/archive/2009/08/20/4467882.aspx

 

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

--  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 行受影响 )

*/