日期:2014-05-18 浏览次数:20711 次
--> --> (Roy)生成測試數據 if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([ID] int,[产品类型] nvarchar(2),[生产厂商] nvarchar(2)) Insert #T select 1,N'电话',N'aa' union all select 2,N'烟',N'bb' union all select 3,N'酒',N'bb' union all select 4,N'电话',N'dd' union all select 5,N'烟',N'cc' union all select 6,N'酒',N'bb' Go --SQL2005 declare @s nvarchar(4000),@s2 nvarchar(4000) set @s2='[生产厂商]' Select @s=isnull(@s+',','')+quotename(产品类型), @s2=isnull(@s2+',','')+quotename(产品类型)+'=isnull('+quotename(产品类型)+',0)' from #T group by 产品类型 print @s2 exec('select '+@s2+' from (select 生产厂商,产品类型,ID=1 from #T) as a pivot (sum(ID) for 产品类型 in('+@s+'))b') /* 生产厂商 电话 酒 烟 aa 1 0 0 bb 0 2 1 cc 0 0 1 dd 1 0 0 */
------解决方案--------------------
静态的: select 生产厂商, sum(case 产品类型 when '电话' then 1 else 0 end) as 电话, sum(case 产品类型 when '酒' then 1 else 0 end) as 酒, sum(case 产品类型 when '烟' then 1 else 0 end) as 烟 from tb group by 生产厂商