请教交叉查询怎么写？有例子最好！在线等！

[hlbasic]

等！！

[wyb0026]

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]

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)

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

[Well]

create table tb1 (id nvarchar(10),type nvarchar(10))
insert into tb1 select '11','a' union all select '22','b' union all select '33','c'
create table tb2 (n int,type nvarchar(10),num int)
insert into tb2 select '1','11','4' union all select '1','11','5'
union all select '2','22','8' union all select '3','22','5'
--查询处理
DECLARE @SQL VARCHAR(8000)
SET @SQL='select n '
SELECT @SQL= @SQL+',sum(case when type='+ttt+' then num else 0 end)['+tt+']' from
(select distinct a.type as tt,isnull(b.type,'0') as ttt from tb2 b right join tb1 a on a.id=b.type) b
set @sql=@sql+' from tb2 group by n'
print @sql
exec(@sql)