楼主你试一下,看行不
create function c_str(@layer varchar(100))
returns varchar(8000)
as
begin
declare @s varcahr(8000)
set @s=''
select @s=@s+','+shop_id from t where right(layer,len(@layer))=@layer
return stuff(@s,1,1,'')
end
select shop_id
,left_num=dbo.c_str('0'+layer)
,right_num=dbo.c_str('1'+layer)
from t
create function c_str(@layer varchar(100))
returns varchar(8000)
as
begin
declare @s varcahr(8000)
set @s=''
select @s=@s+','+shop_id from t where right(layer,len(@layer))=@layer
return stuff(@s,1,1,'')
end
select shop_id
,left_num=dbo.c_str('0'+layer)
,right_num=dbo.c_str('1'+layer)
from t
create table tablename (shop_id varchar(3), num int,layer varchar(100), layer_no int)
insert into tablename values ('001',12,'0',0)
insert into tablename values('002',13,'00',0)
insert into tablename values('003',14,'10',0)
insert into tablename values('004',15,'000',0)
insert into tablename values('005',16,'100',0)
insert into tablename values('006',17,'010',0)
insert into tablename values('007',18,'110',0)
go
--建立一个函数
create function dbo.getvalue(@layer varchar(100),@num varchar(10),@type varchar(10))
returns varchar(1000)
as
begin
declare @hhh varchar(1000)
set @hhh=''
select @hhh=@hhh+convert(varchar(10),num)+',' from tablename
where right(layer,len(@layer)+1) =@type+@layer
set @hhh = case @hhh when '' then '00' else @hhh end
return left(@hhh,case len(@hhh) when 0 then 1 else len(@hhh) end -1)
end
go
--测试
select shop_id,dbo.getvalue(convert(varchar(100),layer),convert(varchar(100),num),'0'),
dbo.getvalue(convert(varchar(100),layer),convert(varchar(100),num),'1') from tablename
--删除测试环境
drop table tablename
drop function dbo.getvalue
create function c_str(@layer varchar(100))
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+','+num from t where right(layer,len(@layer))=@layer
return stuff(@s,1,1,'')
end
select shop_id
,left_num=dbo.c_str('0'+layer)
,right_num=dbo.c_str('1'+layer)
from t
create function c_str(@layer varchar(100))
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+','+cast(num as varchar(100)) from t where right(layer,len(@layer))=@layer
return stuff(@s,1,1,'')
end
select shop_id
,left_num=dbo.c_str('0'+layer)
,right_num=dbo.c_str('1'+layer)
from t