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)