MS SQLServer 交叉報表(行列互換)，mssqlserver

MS SQLServer 交叉報表(行列互換)，mssqlserver

在MS-SQLServer 2005 中可以使用pivot運算子來來實現行列轉換. ,但在之前版本中必須使用 case when then else end 語句

下面以學產生績表來舉例:

id姓名 科目 成績

1 張三 語文 60
2 張三 數學 65
3 張三 外語 70
4 李四 語文 80
5 李四 數學 90
6 李四 外語 85
7 王五 語文 70
8 王五 數學 71
9 王五 外語 75
10 趙六 語文 64
11 趙六 數學 67
12 趙六 外語 76 

查詢後得出:

姓名 語文數學外語

李四 80  90  85
王五 70  71  75
張三 60  65  70
趙六 64  67  76

準備資料:

if exists(select id from sysobjects where xtype='U' and name='studentscore')
drop table studentscore--刪除與實驗衝突的表
go
create table studentscore--建立實驗表
(
[id] int identity(1,1),
[name] nvarchar(20) not null,
subject nvarchar(20) not null,
score int not null
)
go
--添加實驗資料
insert studentscore values ('張三','語文','60');
insert studentscore values ('張三','數學','65');
insert studentscore values ('張三','外語','70');
insert studentscore values ('李四','語文','80');
insert studentscore values ('李四','數學','90');
insert studentscore values ('李四','外語','85');
insert studentscore values ('王五','語文','70');
insert studentscore values ('王五','數學','71');
insert studentscore values ('王五','外語','75');
insert studentscore values ('趙六','語文','64');
insert studentscore values ('趙六','數學','67');
insert studentscore values ('趙六','外語','76');
go
select [id], [name], subject, score from studentscore
go

1 張三 語文 60
2 張三 數學 65
3 張三 外語 70
4 李四 語文 80
5 李四 數學 90
6 李四 外語 85
7 王五 語文 70
8 王五 數學 71
9 王五 外語 75
10 趙六 語文 64
11 趙六 數學 67
12 趙六 外語 76

先利用case when then else end 語句將行轉為列:

select [name],[語文]=sum(case when subject='語文' then score else null end),
[數學]=sum(case when subject='數學' then score else null end),
[外語]=sum(case when subject='外語' then score else null end) 
from studentscore group by [name]

查詢結果：

李四 80 90 85
王五 70 71 75
張三 60 65 70
趙六 64 67 76

以上查詢作用也很大，對於很多情況，比如產品銷售表中按照季度統計、按照月份統計等列頭內容固定的情況，這樣就行了，但往往大多數情況下列頭內容是不固定的，象City，使用者隨時可能刪除、添加一些城市，這種情況就是我們所說的動態交叉表，這個時候需要拼下SQL語句了。

SQLServer中局部變數賦值方法
有兩種: 
一種: set @變數名 = 值 
二種: select @變數名 = 值 

第二種可以從某個表中得到資料再賦值給變數 
例: 從使用者資訊表中查詢中cid為 20 的使用者姓名將他賦值給變數 name 
declare @name varchar(10) --使用者名稱 
select @name=userName from userInfo where cid = 20 
print 'cid為20的使用者姓名:' + @name 

遞迴的select變數：
遞迴的select變數是指使用select語句和子查詢將一個變數與其自身拼接起來。文法形式如下：select @variable = @variable + table.column from table

declare @sql varchar(max)
set @sql = 'select [name],'
select @sql = @sql + 'sum(case subject when '''+subject+''' 
then score else null end) as '''+subject+''','
from (select distinct subject from studentscore) as a 
select @sql = left(@sql,len(@sql)-1) + ' from studentscore group by [name]'
exec(@sql)

執行結果：

李四9085 80
王五 71 75 70
張三 65 70 60
趙六 67 76 64