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:databasewebDB.mdf',
    size = 5mb,
    maxsize = unlimited,
    filegrowth = 10%
)
log on
(
    name = 'webDB_log',
    filename = 'f:databasewebDB_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)