declare @s varchar(8000)
set @s=''
select @s=@s+',[规格'+rtrim(F_TicketType)
+'的数量]=sum(case F_TicketType when '''
++rtrim(F_TicketType)+''' then 1 else 0 end)'
from T_Ticket1 group by F_TicketType
exec('select 客户编号=F_TicketCode'+@s+' from T_Ticket1 group by F_TicketCode')
我觉的上面的方法不能解决这个问题,我想因该要 group by F_CltCode
create table tb(名稱 varchar(10),數量 numeric(10),類型 varchar(5))
Insert into tb
select 'L001','1','A'
union all select 'L001','2','B'
union all select 'L002','5','C'
union all select 'L003','6','D'
union all select 'L004','9','A'
union all select 'L004','5','D'select * from tbdeclare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+max(類型)+']=sum(case 類型 when '''+max(類型)+''' then 數量 else 0 end)'
from tb group by 類型
print @sqlexec('select 名稱'+@sql+' from tb group by 名稱')
--結果
名稱 A B C D
---------------------------------------
L001 1 2 0 0
L002 0 0 5 0
L003 0 0 0 6
L004 9 0 0 5
set @s=''
select @s=@s+',[规格'+rtrim(F_TicketType)
+'的数量]=sum(case b.F_TicketType when '''
++rtrim(F_TicketType)+''' then 1 else 0 end)'
from T_Ticket group by F_TicketType
exec('select 客户编号=a.F_CltCode'+@s+'
from T_Ticket a
left join T_Ticket1 b on a.F_TicketType=b.F_TicketType
and b.F_TicketCode between a.F_BegCode and a.F_EndCode
group by a.F_CltCode')
create table T_Ticket(F_CltCode varchar(10),F_TicketType varchar(10),F_BegCode int,F_EndCode int)
insert T_Ticket select '001','A',1000,2000
union all select '001','A',2500,3000
union all select '001','B',1000,2000
union all select '002','A',2001,2200
union all select '003','C',1000,1200create table T_Ticket1(F_TicketCode int,F_TicketType varchar(10))
insert T_Ticket1 select 1000,'A'
union all select 1001,'A'
union all select 1001,'B'
union all select 1002,'A'
union all select 2001,'A'
union all select 1003,'C'
go--查询
declare @s varchar(8000)
set @s=''
select @s=@s+',[规格'+rtrim(F_TicketType)
+'的数量]=sum(case b.F_TicketType when '''
++rtrim(F_TicketType)+''' then 1 else 0 end)'
from T_Ticket group by F_TicketType
exec('select 客户编号=a.F_CltCode'+@s+'
from T_Ticket a
left join T_Ticket1 b on a.F_TicketType=b.F_TicketType
and b.F_TicketCode between a.F_BegCode and a.F_EndCode
group by a.F_CltCode')
go--删除测试
drop table T_Ticket1,T_Ticket/*--测试结果客户编号 规格A的数量 规格B的数量 规格C的数量
---------- ----------- ----------- -----------
001 3 1 0
002 1 0 0
003 0 0 1
--*/