交叉表实例
建表：
在查询分析器里运行：
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 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
运行结果：
 |
