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
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方法。
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
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
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
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)