请教一句SQL（还是昨天那个问题）
是SQLSERVER2000数据库
表一如下：
ProName ProNum ProColor ProSize
产品1 1 黑 1001
产品1 2 黑 1002
产品1 3 黑 1004
产品1 1 白 1001
产品1 2 白 1002
产品1 3 白 1003
产品2 5 黑 1002
表二如下（记录不固定，有可能只有3条，有可能有10条）：
SizeId SizeName
1001 S
1002 M
1003 L
1004 XL
1005 XXL
要求输出
ProName ProColor S M L XL XXL
产品1 黑 1 2 0 3 0
产品1 白 1 2 3 0 0
产品2 黑 0 5 0 0 0
[解决办法]
--测试环境
create table ttttt(ProName varchar(20),ProNum int,ProColor varchar(20),ProSize varchar(20))
insert ttttt values( '产品1 ',1, '黑 ', '1001 ')
insert ttttt values( '产品1 ',2, '黑 ', '1002 ')
insert ttttt values( '产品1 ',3, '黑 ', '1004 ')
insert ttttt values( '产品1 ',1, '白 ', '1001 ')
insert ttttt values( '产品1 ',2, '白 ', '1002 ')
insert ttttt values( '产品1 ',3, '白 ', '1003 ')
insert ttttt values( '产品2 ',5, '黑 ', '1002 ')
--查看数据
select * from ttttt
--查看结果
declare @sql varchar(2000)
set @sql= 'select ProName,ProColor '
select @sql=@sql+ ',[ '+convert(varchar(20),ProSize)+ ']=isnull(sum(case when ProSize= '+convert(varchar(20),ProSize)+ ' then ProNum end),0) '
from ttttt
group by ProSize
select @sql=@sql+ 'from ttttt group by ProName,ProColor '
print @sql
exec(@sql)
go
drop table ttttt
[解决办法]
--sql server 2000的解决办法 (动态sql)
--创建测试环境
create table 表1(ProName varchar(10),ProNum int,ProColor varchar(10),ProSize varchar(10))
insert into 表1 select '产品1 ',1, '黑 ', '1001 '
union all select '产品1 ',2, '黑 ', '1002 '
union all select '产品1 ',3, '黑 ', '1004 '
union all select '产品1 ',1, '白 ', '1001 '
union all select '产品1 ',2, '白 ', '1002 '
union all select '产品1 ',3, '白 ', '1003 '
union all select '产品2 ',5, '黑 ', '1002 '
go
create table 表2(SizeId varchar(10),SizeName varchar(10))
insert into 表2 select '1001 ', 'S '
union all select '1002 ', 'M '
union all select '1003 ', 'L '
union all select '1004 ', 'XL '
union all select '1005 ', 'XXL '
go
--动态sql
declare @sql varchar(2000)
set @sql= 'select ProName,ProColor '
select @sql=@sql+ ',[ '+SizeName+ ']=max(case when SizeName= ' ' '+SizeName+ ' ' ' then ProNum else 0 end) '
from
(
select top 100 percent ProName,ProNum,ProColor,SizeName,SizeID from 表1 a right join 表2 b
on a.ProSize=b.SizeId
order by b.SizeId
) tb
group by SizeName,SizeID
order by SizeID
set @sql=@sql+ ' from (select ProName,ProNum,ProColor,SizeName from 表1 a inner join 表2 b
on a.ProSize=b.SizeId
) tb
group by ProName,ProColor '
exec(@sql)
--结果
/*
ProName ProColor S M L XL XXL
---------- ---------- ----------- ----------- ----------- ----------- -----------
产品1 白 1 2 3 0 0
产品1 黑 1 2 0 3 0
产品2 黑 0 5 0 0 0
*/
--删除测试环境
drop table 表1,表2
[解决办法]
create table A(ProName varchar(10), ProNum int, ProColor varchar(10), ProSize int)
insert A select '产品1 ', 1, '黑 ', 1001
union all select '产品1 ', 2, '黑 ', 1002
union all select '产品1 ', 3, '黑 ', 1004
union all select '产品1 ', 1, '白 ', 1001
union all select '产品1 ', 2, '白 ', 1002
union all select '产品1 ', 3, '白 ', 1003
union all select '产品2 ', 5, '黑 ', 1002
create table B(SizeId int, SizeName varchar(10))
insert B select 1001, 'S '
union all select 1002, 'M '
union all select 1003, 'L '
union all select 1004, 'XL '
union all select 1005, 'XXL '
declare @sql varchar(8000)
set @sql= 'select ProName, ProColor, '
select @sql=@sql+quotename(SizeName)+ '=max(case when ProSize= '+quotename(SizeId, ' ' ' ')+ ' then ProNum else 0 end), '
from B
select @sql=left(@sql, len(@sql)-1), @sql=@sql+ ' from A group by ProName, ProColor '
exec(@sql)
