详细介绍SQL交叉表的实例
很简单的一个东西，见网上好多朋友问“怎么实现交叉表？”，以下是我写的一个例子，数据库基于SQL SERVER 2000。交叉表实例
建表：
在查询分析器里运行：
CREATE TABLE (
IDENTITY (1, 1) NOT NULL ,
(50) COLLATE Chinese_PRC_CI_AS NULL ,
(50) COLLATE Chinese_PRC_CI_AS NULL ,
(18, 0) NULL
) ON
GO
INSERT INTO (,,) values (N'张三',N'语文',60)
INSERT INTO (,,) values (N'李四',N'数学',70)
INSERT INTO (,,) values (N'王五',N'英语',80)
INSERT INTO (,,) values (N'王五',N'数学',75)
INSERT INTO (,,) values (N'王五',N'语文',57)
INSERT INTO (,,) values (N'李四',N'语文',80)
INSERT INTO (,,) values (N'张三',N'英语',100)
Go
www.ad119.cn/bbs/attachments/computer/20090116/20091178331053177801.jpg
交叉表语句的实现：
用于：交叉表的列数是确定的
select 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
group by name
--用于：交叉表的列数是不确定的
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
运行结果：
www.ad119.cn/bbs/attachments/computer/20090116/20091178331070377802.jpg
<
页:
[1]