22,294
社区成员
发帖
与我相关
我的任务
分享求存储过程
表结构
GoodsCode StoreCode, GoodsSellAmount
a1 1 10
a2 1 15
a1 2 5
a1 3 8
... ... ...
GoodsCode的值要求以参数形式传进去
转换成这种表结构形式显示
GoodsCode storecode1 storecode2 storecode3 ...
a1 10 5 8
a2 15
这种转换怎么写存储过程
GoodsCode StoreCode, GoodsSellAmount
a1 1 10
a2 1 15
a1 2 5
a1 3 8
... ... ...
GoodsCode的值要求以参数形式传进去
转换成这种表结构形式显示
GoodsCode storecode1 storecode2 storecode3 ...
a1 10 5 8
a2 15
这种转换怎么写存储过程
...全文
请发表友善的回复…
发表回复
chenyl0925 2004-01-13
- 打赏
- 举报
再次感谢邹建！
chenyl0925 2004-01-13
- 打赏
- 举报
谢谢各位！借助一临时表可以实现
zjcxc 2004-01-13
- 打赏
- 举报
--下面是测试
--测试数据
create table 表(GoodsCode varchar(2),StoreCode int,GoodsSellAmount int)
insert into 表
select 'a1',1,10
union all select 'a2',1,15
union all select 'a1',2,5
union all select 'a1',3,8
go
--查询处理的存储过程
create proc p_qry
@StoreCodes varchar(1000) --要查询的StoreCode列表
as
declare @s varchar(8000),@qry nvarchar(4000)
set nocount on
select @s=''
,@qry='select @s=@s+'',[storecode''+storecode+'']=sum(case storecode when ''+storecode+'' then GoodsSellAmount else 0 end)''
from(select distinct storecode=cast(storecode as varchar) from 表 where StoreCode in('+@StoreCodes+')) a'
exec sp_executesql @qry,N'@s varchar(8000) out',@s out
exec('select GoodsCode'+@s+' from 表 where StoreCode in('
+@StoreCodes+') group by GoodsCode')
set nocount off
go
--调用示例
exec p_qry '1,2'
go
--删除测试表
drop table 表
drop proc p_qry
/*--测试结果
GoodsCode storecode1 storecode2
--------- ----------- -----------
a1 10 5
a2 15 0
--*/
--测试数据
create table 表(GoodsCode varchar(2),StoreCode int,GoodsSellAmount int)
insert into 表
select 'a1',1,10
union all select 'a2',1,15
union all select 'a1',2,5
union all select 'a1',3,8
go
--查询处理的存储过程
create proc p_qry
@StoreCodes varchar(1000) --要查询的StoreCode列表
as
declare @s varchar(8000),@qry nvarchar(4000)
set nocount on
select @s=''
,@qry='select @s=@s+'',[storecode''+storecode+'']=sum(case storecode when ''+storecode+'' then GoodsSellAmount else 0 end)''
from(select distinct storecode=cast(storecode as varchar) from 表 where StoreCode in('+@StoreCodes+')) a'
exec sp_executesql @qry,N'@s varchar(8000) out',@s out
exec('select GoodsCode'+@s+' from 表 where StoreCode in('
+@StoreCodes+') group by GoodsCode')
set nocount off
go
--调用示例
exec p_qry '1,2'
go
--删除测试表
drop table 表
drop proc p_qry
/*--测试结果
GoodsCode storecode1 storecode2
--------- ----------- -----------
a1 10 5
a2 15 0
--*/
zjcxc 2004-01-13
- 打赏
- 举报
--查询处理的存储过程
create proc p_qry
@StoreCodes varchar(1000) --要查询的StoreCode列表
as
declare @s varchar(8000),@qry nvarchar(4000)
set nocount on
select @s=''
,@qry='select @s=@s+'',[storecode''+storecode+'']=sum(case storecode when ''+storecode+'' then GoodsSellAmount else 0 end)''
from(select distinct storecode=cast(storecode as varchar) from 表 where StoreCode in('+@StoreCodes+')) a'
exec sp_executesql @qry,N'@s varchar(8000) out',@s out
exec('select GoodsCode'+@s+' from 表 where StoreCode in('
+@StoreCodes+') group by GoodsCode')
set nocount off
go
--调用示例
exec p_qry '1,2'
create proc p_qry
@StoreCodes varchar(1000) --要查询的StoreCode列表
as
declare @s varchar(8000),@qry nvarchar(4000)
set nocount on
select @s=''
,@qry='select @s=@s+'',[storecode''+storecode+'']=sum(case storecode when ''+storecode+'' then GoodsSellAmount else 0 end)''
from(select distinct storecode=cast(storecode as varchar) from 表 where StoreCode in('+@StoreCodes+')) a'
exec sp_executesql @qry,N'@s varchar(8000) out',@s out
exec('select GoodsCode'+@s+' from 表 where StoreCode in('
+@StoreCodes+') group by GoodsCode')
set nocount off
go
--调用示例
exec p_qry '1,2'
chenyl0925 2004-01-13
- 打赏
- 举报
表结构
GoodsCode StoreCode, GoodsSellAmount
a1 1 10
a2 1 15
a1 2 5
a1 3 8
... ... ...
如上：仓店总共有20个。我现在只想统计其中任意1--20个仓店的进货情况，这些仓店要求以参数的形式传进去
GoodsCode StoreCode, GoodsSellAmount
a1 1 10
a2 1 15
a1 2 5
a1 3 8
... ... ...
如上：仓店总共有20个。我现在只想统计其中任意1--20个仓店的进货情况，这些仓店要求以参数的形式传进去
zjcxc 2004-01-13
- 打赏
- 举报
没看明白以参数传递进去是什么意思?
要通用的?
举例说明.
要通用的?
举例说明.
realgz 2004-01-13
- 打赏
- 举报
GoodsCode就是key_col了。。
chenyl0925 2004-01-13
- 打赏
- 举报
那就直接 传入
col1,col2,col3
可以解决问题2
1. GoodsCode的值要求以参数形式传进去 怎么解决？
col1,col2,col3
可以解决问题2
1. GoodsCode的值要求以参数形式传进去 怎么解决？
realgz 2004-01-13
- 打赏
- 举报
建议把过程最后一句改成 print @exec
验证是不是你要的代码
验证是不是你要的代码
realgz 2004-01-13
- 打赏
- 举报
那就直接 传入
col1,col2,col3
col1,col2,col3
chenyl0925 2004-01-13
- 打赏
- 举报
to realgz(realgz):
1. GoodsCode的值要求以参数形式传进去
2. --保留的关键字段 如果有几个的话怎么处理?
1. GoodsCode的值要求以参数形式传进去
2. --保留的关键字段 如果有几个的话怎么处理?
realgz 2004-01-13
- 打赏
- 举报
是不是差不多这个意思？
if object_id('make_fun') is not null
drop procedure make_fun
go
create procedure make_fun
(@table_to_turn varchar(255), --待旋转的表
@key_col varchar(255), --保留的关键字段
@col_know_how varchar(255), --生成列名的字段
@col_to_turn varchar(255), --作为值的字段
@how_to varchar(20)='sum') --生成值的方式 sum min max avg ,etc.
/*
过程作用，根据纵向数据生成新横向结构
/
as
declare @exec varchar(8000)
create table #tmp (col varchar(255))
set @exec='select distinct '+@col_know_how+ ' from '+@table_to_turn
insert into #tmp exec (@exec)
set @exec=''
select @exec=@exec+@how_to+'(case when ['+@col_know_how+']= '''+col+''' then ['+@col_to_turn +'] else null end ) as ['+col+'],'
from #tmp
set @exec=left(@exec,len(@exec)-1)
set @exec='select ['+@key_col+'],'+@exec+' from ['+@table_to_turn+'] group by ['+@key_col+']'
exec(@exec)
go
if object_id('make_fun') is not null
drop procedure make_fun
go
create procedure make_fun
(@table_to_turn varchar(255), --待旋转的表
@key_col varchar(255), --保留的关键字段
@col_know_how varchar(255), --生成列名的字段
@col_to_turn varchar(255), --作为值的字段
@how_to varchar(20)='sum') --生成值的方式 sum min max avg ,etc.
/*
过程作用，根据纵向数据生成新横向结构
/
as
declare @exec varchar(8000)
create table #tmp (col varchar(255))
set @exec='select distinct '+@col_know_how+ ' from '+@table_to_turn
insert into #tmp exec (@exec)
set @exec=''
select @exec=@exec+@how_to+'(case when ['+@col_know_how+']= '''+col+''' then ['+@col_to_turn +'] else null end ) as ['+col+'],'
from #tmp
set @exec=left(@exec,len(@exec)-1)
set @exec='select ['+@key_col+'],'+@exec+' from ['+@table_to_turn+'] group by ['+@key_col+']'
exec(@exec)
go
chenyl0925 2004-01-13
- 打赏
- 举报
或者参数“表”以参数的形式传进去也可以，比如说程序中查找得到的一个临时表，怎么写？
chenyl0925 2004-01-13
- 打赏
- 举报
谢谢 zjcxc(邹建) ！GoodsCode的值要求以参数形式传进去怎么做呀。
武哥博文 2004-01-13
- 打赏
- 举报
高，zjcxc(邹建)写的好，学习！！
zjcxc 2004-01-13
- 打赏
- 举报
--下面是测试
--测试数据
create table 表(GoodsCode varchar(2),StoreCode int,GoodsSellAmount int)
insert into 表
select 'a1',1,10
union all select 'a2',1,15
union all select 'a1',2,5
union all select 'a1',3,8
go
--查询处理
declare @s varchar(8000)
set @s=''
select @s=@s+',[storecode'+storecode+']=sum(case storecode when '+storecode+' then GoodsSellAmount else 0 end)'
from(select distinct storecode=cast(storecode as varchar) from 表) a
exec('select GoodsCode'+@s+' from 表 group by GoodsCode')
go
--删除测试表
drop table 表
/*--测试结果
GoodsCode storecode1 storecode2 storecode3
--------- ----------- ----------- -----------
a1 10 5 8
a2 15 0 0
--*/
--测试数据
create table 表(GoodsCode varchar(2),StoreCode int,GoodsSellAmount int)
insert into 表
select 'a1',1,10
union all select 'a2',1,15
union all select 'a1',2,5
union all select 'a1',3,8
go
--查询处理
declare @s varchar(8000)
set @s=''
select @s=@s+',[storecode'+storecode+']=sum(case storecode when '+storecode+' then GoodsSellAmount else 0 end)'
from(select distinct storecode=cast(storecode as varchar) from 表) a
exec('select GoodsCode'+@s+' from 表 group by GoodsCode')
go
--删除测试表
drop table 表
/*--测试结果
GoodsCode storecode1 storecode2 storecode3
--------- ----------- ----------- -----------
a1 10 5 8
a2 15 0 0
--*/
zjcxc 2004-01-13
- 打赏
- 举报
declare @s varchar(8000)
set @s=''
select @s=@s+',[storecode'+storecode+']=sum(case storecode when '+storecode+' then GoodsSellAmount else 0 end)'
from(select distinct storecode=cast(storecode as varchar) from 表) a
exec('select GoodsCode'+@s+' from 表 group by GoodsCode')
set @s=''
select @s=@s+',[storecode'+storecode+']=sum(case storecode when '+storecode+' then GoodsSellAmount else 0 end)'
from(select distinct storecode=cast(storecode as varchar) from 表) a
exec('select GoodsCode'+@s+' from 表 group by GoodsCode')
