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 @[email protected]+','+'[ShopId'+LTRIM([ShopId])+']=sum(case when ShopId='+ LTRIM(ShopId)+' then 1 else 0 end)'from [test]group by ShopIdexec('select [ProductId][email protected]+' from test group by [ProductId]')/*ProductId ShopId1 ShopId2 ShopId31 1 0 12 1 1 03 1 0 1*/