SQL Server: 行列轉化

列 轉為 行：  

1. max + group by 

create table tb1(id int,name varchar(20),book varchar(20),date datetime) insert tb1 values(1,'JOKE','SHUXUE','2004-12-12')

create table tb2(s_id varchar(10),id int,s_likeman varchar(20),pricre int) insert tb2 values('0001',1,'小?',12) insert tb2 values('0002',1,'小李',13)

declare @sql varchar(8000) set @sql='select a.book' select @sql=@sql+',max(case when b.s_likeman='''+s_likeman+''' then b.s_likeman end) s_likeman,           max(case when b.pricre='+cast(pricre as varchar)+' then b.pricre end) pricre' from (select distinct s_likeman,pricre from tb2) a

exec (@sql+' from tb1 a,tb2 b where a.id=b.id group by a.book')

drop table tb1,tb2

 

2.SQL中行列轉換函數(Pivot UnPivot)

完整語法：
table_source
PIVOT(
聚合函數（value_column）
FOR pivot_column
IN(<column_list>)
)

完整語法：
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)

注意：PIVOT、UNPIVOT是SQL Server 2005 的用法

一、PIVOT

1、建立表格

if object_id('tb') is not null drop table tb

go create table tb(姓名 varchar(10),課程 varchar(10),分數 int)

insert into tb values('張三','語文',74)

insert into tb values('張三','數學',83) insert into tb values('張三','物理',93) insert into tb values('李四','語文',74) insert into tb values('李四','數學',84)

insert into tb values('李四','物理',94)

2、使用pivot   select* from tb pivot(max(分數) for 課程 in (語文,數學,物理)) a

姓名       語文        數學        物理 

李四        74          84          94

張三        74          83          93

3、使用pivot  + stuff()

declare @sqlvarchar(8000) set @sql='' 

select @sql=@sql+','+課程 from tb group by 課程

set@sql=stuff(@sql,1,1,'')--去掉首個','

set@sql='select * from tb pivot (max(分數) for 課程 in ('+@sql+')) a'

exec(@sql)

--或使用isnull()

declare @sqlvarchar(8000)

select @sql=isnull(@sql+',','')+課程 from tb group by 課程          

set @sql='select * from tb pivot (max(分數) for 課程 in ('+@sql+')) a'

exec(@sql)

 行 轉為 列：

1. UNPIVOT：select * from tb UNPIVOT ( 分數 FOR 課程 IN (語文,數學,物理)) a

2. 將多行轉為1列：

--> 生成表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL     DROP TABLE [tb] GO

CREATE TABLE tb ([Name] varchar(10),[Type] varchar(10))

INSERT INTO tb SELECT 'A','PR1' UNION ALL SELECT 'A','PR2' UNION ALL SELECT 'B','PR1' UNION ALL SELECT 'B','PR2' UNION ALL SELECT 'A','PR3'

/* Name       TYPE

---------- -----------------------

A　　　　PR1

A　　　　PR2

A　　　　PR3

B　　　　PR1

B　　　　PR2

*/

SELECT DISTINCT Name,  TYPE=STUFF((SELECT ';'+ TYPE FROM tb WHERE Name=t.Name FOR XML PATH('')),1,1,'') FROM [tb] t

/* Name       TYPE

---------- -----------------------

A          PR1;PR2;PR3

B          PR1;PR2

(2 行受影?) */