将查询结果特殊处理
select * from Def_Shop 

id Nmae
------------
1 店1
2 店2
3 店3
4 店4
....有可能更多

select * from PRO_StorageRecord

id ShopId ProductId Quantity
--------------------------------------
1 1 1 1  
2 1 2 2
3 1 3 1
4 2 2 1
5 3 1 3
6 3 3 1
如何转换为下面结果
ProductId ShopId1 ShopId2 ShopId3 ShopId4
------------------------
1 1 0 3 0
2 2 1 0 0
3 1 0 1 0


------解决方案--------------------
标准行列转化啊。 这LZ参考精华帖吧。

------解决方案--------------------

SQL code

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] int,[ShopId] int,[ProductId] int,[Quantity] int)
insert [test]
select 1,1,1,1 union all
select 2,1,2,2 union all
select 3,1,3,1 union all
select 4,2,2,1 union all
select 5,3,1,3 union all
select 6,3,3,1
declare @str varchar(8000)
set @str=''
select 
      @str=@str+','+'[ShopId'+LTRIM([ShopId])+']=sum(case when ShopId='+
      LTRIM(ShopId)+' then 1 else 0 end)'
from 
     [test]
group by 
     ShopId
exec('select [ProductId]'+@str+' from test group by [ProductId]')

/*
ProductId    ShopId1    ShopId2    ShopId3
1    1    0    1
2    1    1    0
3    1    0    1
*/