12、SQL Server 行列转换

SQLServer行转列

在SQL Server 2005中PIVOT 用于将列值转换为列名(行转列)，在SQL Server 2000中是没有这个关键字的 只能用case语句实现。

--创建测试数据库use mastergoif ( exists (select * from sys.databases where name = 'webDB') )    drop database webDBgocreate database webDB on PRimary(    name = 'webDB',    filename = 'f:/database/webDB.mdf',    size = 5mb,    maxsize = unlimited,    filegrowth = 10%)log on(    name = 'webDB_log',    filename = 'f:/database/webDB_log.ldf',    size = 3mb,    maxsize = 50mb,    filegrowth = 2mb)use webDBgo--创建测试表if( exists ( select * from sys.objects where name = 'student'))    drop table studentgocreate table student(    id int identity(1,1) primary key,    name varchar(20) not null,    subject varchar(20) not null,    score int not null)    --插入测试数据insert into student values ('张三','语文',90),('张三','数学',100),('张三','英语',80),('李四','英语',90),('王五','语文',90),('李四','语文',90),('李四','数学',70),('王五','数学',62),('王五','英语',82)select * from student

SQL Server 2000行转列

select name as 姓名,SUM(case [subject] when '语文' then score else 0 end) as '语文',SUM(case [subject] when '数学' then score else 0 end ) as '数学',SUM(case [subject] when '英语' then score else 0 end ) as '英语'from student group by name

如图所示，已经按照脚本中指定的列名进行转换，但这样做需要知道表中都有哪些数据可以作为列。通常将这种方法称为静态方法。

declare @sql varchar(1000)set @sql = 'select name as 姓名 , 'select @sql = @sql + 'sum(case [subject] when ''' + [subject] + ''' then score  else 0 end ) as '''+ QUOTENAME([subject]) + ''',' from (select distinct [subject] from student ) as s --后加逗号，然后截取最后一个逗号select @sql = LEFT(@sql,len(@sql)-1) + ' from student group by name 'print(@sql)exec(@sql)select QUOTENAME('aa[]bb') --其中quotename 用于将字符串为有效的标识符

这种方法不需要知道到底需要将哪些数据作为列转换，它会自动去数据中查找不重复的数据，都会作为列来显示。通常将这种方法称为动态方法，拼接sql方法。

SQLServer 2005行转列

select * from (    select name,[subject],score from student) s pivot (sum(score) for [subject] in (语文,数学,英语)) as pvtorder by pvt.name

PIVOT语法是：PIVOT(聚合函数(列) for 列 in (值,值,值)) as p

这个是静态方法行转列，怎么样代码简洁吧。

declare @sql_str varchar(1000)declare @sql_col varchar(1000)select @sql_col = ISNULL(@sql_col + ',','') + QUOTENAME([subject]) from student group by [subject] --先确定要转换的列名set @sql_str = 'select * from (    select name,[subject],score from student ) s pivot (sum(score) for [subject] in (' + @sql_col + ')) as pvtorder by pvt.name'print(@sql_str)exec(@sql_str)

以上2005中动态创建方法。

SQL Server列转行

在SQL Server 2005中UNPIVOT用于将列名转换为值（列转行），在SQL Server 2000中只能用UNION语句实现。

use webDBgo--创建测试表if( exists ( select * from sys.objects where name = 'student'))    drop table studentgocreate table student(    id int identity(1,1) primary key,    name varchar(20) not null,    语文 int not null,    英语 int not null,    数学 int not null)    --插入测试数据insert into student values ('张三',87,90,62),('李四',87,90,65),('王五',23,90,34)select * from student

SQLServer 2000中列转行

SQLServer 2000静态方法

select * from (    select name,课程='语文',分数=语文 from student    union all    select name,课程='数学',分数=数学 from student    union all    select name,课程='英语',分数=英语 from student) t order by name, case 课程 when '语文' then 1 when '数学' then 2 when '英语' then 3 end

SQLServer 2000动态SQL

declare @sql varchar(1000)select @sql = ISNULL(@sql + ' union all ','') + ' select name,课程='+ QUOTENAME(name,'''')+' , 分数 = ' + QUOTENAME(name) + ' from student' from syscolumns where id=object_id('student') and name not in ('id','name')print(@sql)exec(@sql)

SQL Server 2005 静态SQL使用UNPIVOT关键字

select name,课程,分数 from student unpivot (分数 for 课程 in (语文,英语,数学)) s

SQLServer 2005动态SQL

declare @sql varchar(1000)select @sql = isnull(@sql + ',','') + quotename(name) from syscolumns where id = object_id('student') and name not in ('id','name')order by colidset @sql = 'select name,课程,分数 from student unpivot (分数 for 课程 in ('+@sql+')) s'print(@sql)exec(@sql)