将查询结果特殊处理

[jishengzu]

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

[jishengzu]

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
 

[Mr_Nice]

标准行列转化啊。 这LZ参考精华帖吧。

[开着拖拉机泡妞]

--> 测试数据：[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
*/

[jishengzu]

3q 3q

[开着拖拉机泡妞]

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([id] int,[Nmae] varchar(3))
insert [tbl]
select 1,'店1' union all
select 2,'店2' union all
select 3,'店3' union all
select 4,'店4'

--> 测试数据：[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


select a.id,a.ProductId,isnull(b.Quantity,0) as Quantity into #t 
from(
select * from(
select distinct id from tbl)t
cross join (select distinct [ProductId] from test)m)a
left join test b
on a.id=b.ShopId and a.ProductId=b.ProductId

select * from #t


declare @str varchar(8000)
set @str=''
select 
      @str=@str+','+'[shopId'+LTRIM(id)+']=sum(case when id='+
      LTRIM(id)+' then isnull(Quantity,0) else 0 end)'
from 
     #t
group by 
     id
exec('select [ProductId]'+@str+' from #t group by [ProductId]')

/*
ProductId	shopId1	shopId2	shopId3	shopId4
1	1	0	3	0
2	2	1	0	0
3	1	0	1	0
*/