交叉表的概念及sql中交叉表的使用

交叉表是一種常用的分類匯總表格。使用交叉表查詢，顯示源于表中某個字段的匯總值，并將它們分組，其中一組列在數據表的左側，另一組列在數據表的上部。行和列的交叉處可以對數據進行多種匯總計算，如：求和、平均值、記數、最大值、最小值等。使用交叉表查詢數據非常直觀明了，被廣泛應用。交叉表查詢也是數據庫的一個特點。

CREATE TABLE [Test] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[Source] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

INSERT INTO [test] ([name],[subject],[Source]) values (N'張三',N'語文',60)

INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'數學',70)

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英語',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'數學',75)

INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'語文',57)

INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'語文',80)

INSERT INTO [test] ([name],[subject],[Source]) values (N'張三',N'英語',100)

Go

SELECT * FROM TEST
ID NAME SUBJECT SOURCE
1 張三 語文 60
2 李四 數學 70
3 王五 英語 80
4 王五 數學 75
5 王五 語文 57
6 李四 語文 80
7 張三 英語 100

一.確定列,查詢交叉表
SELECT A.NAME,
SUM(CASE SUBJECT WHEN '語文' THEN SOURCE ELSE 0 end) as 語文,
SUM(CASE SUBJECT WHEN '數學' THEN SOURCE ELSE 0 end) as 數學,
SUM(CASE SUBJECT WHEN '英語' THEN SOURCE ELSE 0 end) as 英語
FROM test as A
group by a.name
reslut：
name 語文 數學 英語
李四 80 70 0
王五 57 75 80
張三 60 0 100

二.列數不確定,需要聲明變量來獲取要列出的科目.

declare @sql varchar(8000)

set @sql = 'select name,'

select @sql = @sql + 'sum(case subject when '''+subject+'''

then source else 0 end) as '''+subject+''','

from (select distinct subject from test) as a

select @sql = left(@sql,len(@sql)-1) + ' from test group by name'

exec(@sql)

go