列横向显示的问题
表T
供应商 产品名 报价
G1 C1 10
G2 C1 11
G3 C1 12
G1 C2 10
G2 C2 11
G3 C2 12
G1 C3 14
G3 C3 12
我现在要把供应商列横向显示该怎么做?
假如我在不知供应商数量的条件下用动态SQL生成;
产品名 G1 G2 G3
C1 10 11 12
C2 10 11 12
C3 14 0 12
我看邹建的书 < <SQL Server200 开发与管理> > 上有相知的例子,
但是没看懂!唉~
[解决办法]
Declare @S Nvarchar(4000)
Select @S = ' Select 产品名 '
Select @S = @S + ', SUM(Case 供应商 When ' ' ' + 供应商 + ' ' ' Then 报价 Else 0 End) As ' + 供应商
From T Group By 供应商
Select @S = @S + ' From T Group By 产品名 Order By 产品名 '
EXEC(@S)
[解决办法]
create table test (vendor char(2),[group] char(2),price decimal(10,1))
insert test select 'G1 ', 'C1 ',10.0
union all select 'G2 ', 'C1 ',11.0
union all select 'G3 ', 'C1 ',12.0
union all select 'G1 ', 'C2 ',10.0
union all select 'G2 ', 'C2 ',11.0
union all select 'G3 ', 'C2 ',12.0
union all select 'G1 ', 'C3 ',14.0
union all select 'G3 ', 'C3 ',12.0
Declare @s nvarchar(4000)
set @s= 'select [group] '
select @s=@s + ', '+quotename(vendor)
+N '=sum(case vendor when '+quotename(vendor,N ' ' ' ')
+N ' then price end) '
from test
group by vendor
exec (@s+N '
from test
group by [group] ')
drop table test
group G1 G2 G3
C110.011.012.0
C210.011.012.0
C314.0NULL12.0
