将查询结果特殊处理,该如何解决

将查询结果特殊处理
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 allselect 2,1,2,2 union allselect 3,1,3,1 union allselect 4,2,2,1 union allselect 5,3,1,3 union allselect 6,3,3,1declare @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      ShopIdexec('select [ProductId]'+@str+' from test group by [ProductId]')/*ProductId    ShopId1    ShopId2    ShopId31    1    0    12    1    1    03    1    0    1*/