你的位置:首页 > 数据库

[数据库]12、SQL Server 行列转换


SQL Server 行转列

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

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

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