交叉表的概念及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