很简单的一个东西见网上好多朋友问怎么实现交叉表？以下是我写的一个例子数据库基于SQL SERVER
交叉表实例
建表
在查询分析器里运行
CREATE TABLE [Test] (
[id] [int] IDENTITY ( ) NOT NULL
[name] [nvarchar] () COLLATE Chinese_PRC_CI_AS NULL
[subject] [nvarchar] () COLLATE Chinese_PRC_CI_AS NULL
[Source] [numeric]( ) NULL
) ON [PRIMARY]
GO
INSERT INTO [test] ([name][subject][Source]) values (N张三N语文)
INSERT INTO [test] ([name][subject][Source]) values (N李四N数学)
INSERT INTO [test] ([name][subject][Source]) values (N王五N英语)
INSERT INTO [test] ([name][subject][Source]) values (N王五N数学)
INSERT INTO [test] ([name][subject][Source]) values (N王五N语文)
INSERT INTO [test] ([name][subject][Source]) values (N李四N语文)
INSERT INTO [test] ([name][subject][Source]) values (N张三N英语)
Go
交叉表语句的实现
用于交叉表的列数是确定的
select namesum(case subject when 数学 then source else end) as 数学
sum(case subject when 英语 then source else end) as 英语
sum(case subject when 语文 then source else end) as 语文
from test
group by name
用于交叉表的列数是不确定的
declare @sql varchar()
set @sql = select name
select @sql = @sql + sum(case subject when +subject+
then source else end) as +subject+
from (select distinct subject from test) as a
select @sql = left(@sqllen(@sql)) + from test group by name
exec(@sql)
go
运行结果
