用SQL语句实现交叉表

[liangjuan01]

3、 用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 ('张三','语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values ('李四','数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values ('李四','语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values ('张三','英语',100)
Go
输出结果的为：name 数学　英语　语文
张三 0 100 60
李四 70 0 80
王五 75 80 57

[jinjazz]

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 ('张三','语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values ('李四','数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values ('王五','语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values ('李四','语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values ('张三','英语',100)
Go
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT name'
SELECT @SQL= @SQL+ ',max(CASE WHEN subject = ''' + subject + ''' THEN Source else 0 END) [' + subject + ']' FROM (SELECT DISTINCT subject FROM Test) A
SET @SQL=@SQL+' FROM Test GROUP BY name'
exec (@SQL)

/*
name 数学 英语 语文
李四 70 0 80
王五 75 80 57
张三 0 100 60
*/
drop table Test

[wyb0026]

select name , sum(case when subject='数学' then Source else 0 end) 数学
,sum(case when subject='英语' then Source else 0 end) 英语
,sum(case when subject='语文' then Source else 0 end) 语文
from test
group by name