没测试.... declare @s varchar(8000)set @s='' select @s=@s+',['+商品+']=max(case when 商品='+cast(商品 as varchar)+' then 货物量 else 0 end)' from t group by 商品exec('select 商店,分仓'+@s+' from t group by 分仓,商店 order by 商店')
老大不行呀 测试库 create table A(skuid varchar(20),RDC varchar(20),soldto varchar(20),total varchar(20)) insert A select '1','A' ,'5','22' union all select '2','A','5','60' union all select '1','B','1', '34' union all select '2','B','2','100' union all select '2','D','4','80' union all select'1','D', '1','34' union all select'3','D', '2','100' union all select'4','D , '2', '80'go
declare @s varchar(8000)set @s='' select @s=@s+',[商品'+skuid+']=max(case when skuid='''+skuid+''' then total else '''' end)' from A group by skuidexec('select rdc,soldto'+@s+' from A group by rdc,soldto order by rdc')
declare @sql varchar(3000) set @sql='select 商店,分仓' select @sql=@sql+',max(case 商品 when '''+商品+''' then 货物量 else 0 end)[商品'+商品+']' from (select distinct 商品 from 表)a set @sql=@sql+' from 表 group by 商店,分仓' exec(@sql)
忘说了 还有一个月分字段 DATA 一个行列转换的问题,想了一天没结果,望高手指教 有如下表: 商品(sku)商店(ship) 分仓(rdc) 货物量(total) DATA 1 A 5 22 200411 1 A 2 80 200412 2 A 5 60 200411 1 B 1 34 200411 2 B 2 100 200411 2 D 4 80 200411 1 D 1 34 200412 3 D 2 100 200411 4 D 2 80 200411...... 此表中商品,商店, 分仓的个数不确定,同一个商品可能在不同商店的不同分仓,同一个分仓也对应不同的商店和不同的商品,时间不同的要分开现要转换成如下格式:
商店(ship) 分仓(rdc) DATA 商品1(sku) 商品2(sku) 商品3(sku) A 5 200411 22 60 A 2 200412 80 B 1 200411 34 B 2 200411 100 D 4 200411 80 D 1 200412 34 D 2 200411 100 测试库 create table A(skuid varchar(20),RDC varchar(20),soldto varchar(20),total varchar(20),DATA varchar(20),) insert A select '1','A' ,'5','22','200411' union all select '2','A','5','60','200412' union all select '1','B','1', '34','200411' union all select '2','B','2','100','200411' union all select '2','D','4','80','200411' union all select'1','D', '1','34','200412' union all select'3','D', '2','100','200411' union all select'4','D , '2', '80','200411'go
declare @sql varchar(3000) set @sql='select soldto as 商品,RDC as 分仓' select @sql=@sql+',max(case skuid when '''+skuid+''' then total else 0 end)[商品'+skuid+']' from (select distinct skuid from A)a set @sql=@sql+' from A group by RDC,soldto' exec(@sql) ----------------------- 1 B 34 0 0 0 1 D 34 0 0 0 2 B 0 100 0 0 2 D 0 0 100 80 4 D 0 80 0 0 5 A 22 60 0 0
declare @sql varchar(3000) set @sql='select soldto as 商品,RDC as 分仓,Date' select @sql=@sql+',max(case skuid when '''+skuid+''' then total else 0 end)[商品'+skuid+']' from (select distinct skuid from A)a set @sql=@sql+' from A group by RDC,soldto,Date' exec(@sql) -----------------------
declare @s varchar(8000)set @s=''
select @s=@s+',['+商品+']=max(case when 商品='+cast(商品 as varchar)+' then 货物量 else 0 end)'
from t
group by 商品exec('select 商店,分仓'+@s+' from t group by 分仓,商店 order by 商店')
测试库
create table A(skuid varchar(20),RDC varchar(20),soldto varchar(20),total varchar(20))
insert A select '1','A' ,'5','22'
union all select '2','A','5','60'
union all select '1','B','1', '34'
union all select '2','B','2','100'
union all select '2','D','4','80'
union all select'1','D', '1','34'
union all select'3','D', '2','100'
union all select'4','D , '2', '80'go
select @s=@s+',[商品'+skuid+']=max(case when skuid='''+skuid+''' then total else '''' end)'
from A
group by skuidexec('select rdc,soldto'+@s+' from A group by rdc,soldto order by rdc')
set @sql='select 商店,分仓'
select @sql=@sql+',max(case 商品 when '''+商品+''' then 货物量 else 0 end)[商品'+商品+']'
from (select distinct 商品 from 表)a
set @sql=@sql+' from 表 group by 商店,分仓'
exec(@sql)
一个行列转换的问题,想了一天没结果,望高手指教
有如下表:
商品(sku)商店(ship) 分仓(rdc) 货物量(total) DATA
1 A 5 22 200411
1 A 2 80 200412
2 A 5 60 200411
1 B 1 34 200411
2 B 2 100 200411
2 D 4 80 200411
1 D 1 34 200412
3 D 2 100 200411
4 D 2 80 200411......
此表中商品,商店, 分仓的个数不确定,同一个商品可能在不同商店的不同分仓,同一个分仓也对应不同的商店和不同的商品,时间不同的要分开现要转换成如下格式:
商店(ship) 分仓(rdc) DATA 商品1(sku) 商品2(sku) 商品3(sku)
A 5 200411 22 60
A 2 200412 80
B 1 200411 34
B 2 200411 100
D 4 200411 80
D 1 200412 34
D 2 200411 100
测试库
create table A(skuid varchar(20),RDC varchar(20),soldto varchar(20),total varchar(20),DATA varchar(20),)
insert A select '1','A' ,'5','22','200411'
union all select '2','A','5','60','200412'
union all select '1','B','1', '34','200411'
union all select '2','B','2','100','200411'
union all select '2','D','4','80','200411'
union all select'1','D', '1','34','200412'
union all select'3','D', '2','100','200411'
union all select'4','D , '2', '80','200411'go
set @sql='select soldto as 商品,RDC as 分仓'
select @sql=@sql+',max(case skuid when '''+skuid+''' then total else 0 end)[商品'+skuid+']'
from (select distinct skuid from A)a
set @sql=@sql+' from A group by RDC,soldto'
exec(@sql)
-----------------------
1 B 34 0 0 0
1 D 34 0 0 0
2 B 0 100 0 0
2 D 0 0 100 80
4 D 0 80 0 0
5 A 22 60 0 0
set @sql='select soldto as 商品,RDC as 分仓,Date'
select @sql=@sql+',max(case skuid when '''+skuid+''' then total else 0 end)[商品'+skuid+']'
from (select distinct skuid from A)a
set @sql=@sql+' from A group by RDC,soldto,Date'
exec(@sql)
-----------------------