交叉表的问题（急求！100分！！！！！！）
if exists (select * from dbo.sysobjects where id = object_id(N '[tb] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [tb]
GO
create table tb(projectid varchar(10), 6d varchar(10), model varchar(10), qty int)
insert into tb
select 'P1 ', '6DA ', 'A ',1
union all select 'P1 ', '6DA ', 'B ',2
union all select 'P1 ', '6DA ', 'C ',3
union all select 'P1 ', '6DB ', 'D ',4
union all select 'P1 ', '6DB ', 'E ',5
union all select 'P1 ', '6DC ', 'F ',6
union all select 'P2 ', '6DA ', 'G ',7
union all select 'P2 ', '6DB ', 'H ',8
union all select 'P2 ', '6DD ', 'I ',9
union all select 'P3 ', '6DC ', 'J ',10
union all select 'P3 ', '6DD ', 'K ',11
union all select 'P3 ', '6DE ', 'L ',12
---------------------
显示结果：
---------------------
-------|-----------------------------
| 6DA | 6DB | 6DC | 6DD | 6DE |
Project|--------------------|---------------|----------|----------|-----|
| A B C G | D E H | F J | I K | L |
------------------------------------|
P1 | 1 2 3 0 | 4 5 0 | 0 0 | 0 0 | 0 |
P2 | 0 0 0 7 | 0 0 8 | 0 0 | 9 0 | 0 |
P3 | 0 0 0 0 | 0 0 0 | 0 10 | 0 11 | 12 |
-------------------------------------
[解决办法]
--交叉表结果集,就这么用.
declare @s varchar(2000)
set @s= 'select projectid '
select @s=@s+ ',[ '+model+ ']=sum(case when model= ' ' '+model+ ' ' ' then qty else 0 end) ' from tb
group by [6d],model
set @s=@s+ ' from tb group by projectid '
exec(@s)
--表头,就前台处理.
[解决办法]
declare @s varchar(2000)
set @s= 'select projectid '
select @s=@s+ ',[ '+[6d]+ '- '+model+ ']=sum(case when [6d]= ' ' '+[6d]+ ' ' ' and model= ' ' '+model+ ' ' ' then qty else 0 end) ' from tb
group by [6d],model order by [6d],model
set @s=@s+ ' from tb group by projectid '
exec(@s)
