转载

12、SQL Server 行列转换

SQL Server 行转列

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

--创建测试数据库 use master go if ( exists (select * from sys.databases where name = 'webDB') )  drop database webDB go create 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 webDB go --创建测试表 if( exists ( select * from sys.objects where name = 'student'))  drop table student go create 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

12、SQL Server 行列转换

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

12、SQL Server 行列转换

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

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 用于将字符串为有效的标识符

12、SQL Server 行列转换

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

SQL Server 2005 行转列

select * from (     select name,[subject],score from student ) s pivot (sum(score) for [subject] in (语文,数学,英语)) as pvt order 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 pvt order by pvt.name' print(@sql_str) exec(@sql_str)

以上2005中动态创建方法。

SQL Server 列转行

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

use webDB go --创建测试表 if( exists ( select * from sys.objects where name = 'student'))  drop table student go create 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

12、SQL Server 行列转换

SQL Server 2000中列转行

SQL Server 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

12、SQL Server 行列转换

SQL Server 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

12、SQL Server 行列转换

SQL Server 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 colid set @sql = 'select name,课程,分数 from student unpivot (分数 for 课程 in ('+@sql+')) s' print(@sql) exec(@sql)