create function fn_test(@col1 int) reurn varchar(100) as begin declare @str varchar(100) set @str = '' select @str = @str + '/' +列名1 from B where 列名2 = '@col1' set @str = stuff('',1,1); return @str endgo select 列名1 ,列名3 = dbo.fn_test(名1) from A
对了,还要去除重复,比如列名1包含S1 S2 S3 S1 则显示只需要显示s1/s2/s3
create function fn_test(@col1 int) reurn varchar(100) as begin declare @str varchar(100) set @str = ' ' select @str = @str + '/ ' +列名1 from (select distinct 列名1 from B where 列名2 = '@col1 ' ) G set @str = stuff(@str,1,1,''); return @str end go select 列名1 ,列名3 = dbo.fn_test(名1) from A
create function fn_test(@col1 int) reurn varchar(100) as begin declare @str varchar(100) set @str = ' ' select @str = @str + '/ ' +列名1 from (select distinct 列名1 from b ) bb where 列名2 = '@col1 ' set @str = stuff( ' ',1,1); return @str end go select 列名1 ,列名3 = dbo.fn_test(名1) from A
create function test(@col varchar(100)) reurn varchar(100) as begin declare @str varchar(100) set @str = ' ' select @str = @str + '/ ' +列名1 from B where 列名2 = '@col' set @str = right( @str,len(@str)-1) return @str end go select 列名1 ,'列名3' = dbo.test(名1) from A
create function test(@col varchar(100)) reurn varchar(100) as begin declare @str varchar(100) set @str = ' ' select @str = @str + '/ ' +列名1 from (select distinct 列名1 from b ) bb where bb.列名2 = '@col ' set @str = right( @str,len(@str)-1) return @str end go select 列名1 , '列名3 ' = dbo.test(名1) from A
/* A表 列名1 列名2 1 xxx B表 列名1 列名2 s1 1 s2 1 s3 1 其中A表的列名1是B表列名2的外键 我现在想显示如下信息 列名1 列名3 1 s1/s2/s3 请问如何写这样的SQL语句 */use pubs gocreate table ta ( col1 varchar(2), col2 int ) create table tb ( col1 varchar(2), col3 int ) insert into tb values('s1',1) insert into tb values('s2',1) insert into tb values('s3',1)goif object_id('pubs..f_hb') is not null drop function f_hb go--创建一个合并的函数 create function f_hb(@a int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + '/' + col1 from tb where col3= @a set @str = right(@str , len(@str) - 1) return(@str) End go--调用自定义函数得到结果: select distinct col3 ,dbo.f_hb(col3) as col3 from tbdrop table tb drop table ta drop function f_hb/* col1 col3 ------------------ 1 s1/s2/s3*//* 此程序参考http://topic.csdn.net/u/20071115/11/1a130a85-7801-45dc-8ec8-1a5c4ca3dbfc.html */
A表 列名1 列名2 1 xxx B表 列名1 列名2 s1 1 s2 1 s3 1 declare @test as varchar(100) set @test="" select 列名1,(select @test =@test +'\'from A left join B where A.列名1=B.列名2) from B
reurn varchar(100)
as
begin
declare @str varchar(100)
set @str = ''
select @str = @str + '/' +列名1 from B where 列名2 = '@col1'
set @str = stuff('',1,1);
return @str
endgo
select 列名1 ,列名3 = dbo.fn_test(名1) from A
reurn varchar(100)
as
begin
declare @str varchar(100)
set @str = ' '
select @str = @str + '/ ' +列名1 from (select distinct 列名1 from B where 列名2 = '@col1 ' ) G
set @str = stuff(@str,1,1,'');
return @str
end go
select 列名1 ,列名3 = dbo.fn_test(名1) from A
reurn varchar(100)
as
begin
declare @str varchar(100)
set @str = ' '
select @str = @str + '/ ' +列名1 from (select distinct 列名1 from b ) bb where 列名2 = '@col1 '
set @str = stuff( ' ',1,1);
return @str
end go
select 列名1 ,列名3 = dbo.fn_test(名1) from A
reurn varchar(100)
as
begin
declare @str varchar(100)
set @str = ' '
select @str = @str + '/ ' +列名1 from B where 列名2 = '@col'
set @str = right( @str,len(@str)-1)
return @str
end go
select 列名1 ,'列名3' = dbo.test(名1) from A
reurn varchar(100)
as
begin
declare @str varchar(100)
set @str = ' '
select @str = @str + '/ ' +列名1 from (select distinct 列名1 from b ) bb where bb.列名2 = '@col '
set @str = right( @str,len(@str)-1)
return @str
end go
select 列名1 , '列名3 ' = dbo.test(名1) from A
A表
列名1 列名2
1 xxx B表
列名1 列名2
s1 1
s2 1
s3 1 其中A表的列名1是B表列名2的外键 我现在想显示如下信息
列名1 列名3
1 s1/s2/s3 请问如何写这样的SQL语句
*/use pubs
gocreate table ta
(
col1 varchar(2),
col2 int
)
create table tb
(
col1 varchar(2),
col3 int
)
insert into tb values('s1',1)
insert into tb values('s2',1)
insert into tb values('s3',1)goif object_id('pubs..f_hb') is not null
drop function f_hb
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '/' + col1 from tb where col3= @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct col3 ,dbo.f_hb(col3) as col3 from tbdrop table tb
drop table ta
drop function f_hb/*
col1 col3
------------------
1 s1/s2/s3*//*
此程序参考http://topic.csdn.net/u/20071115/11/1a130a85-7801-45dc-8ec8-1a5c4ca3dbfc.html
*/
列名1 列名2
1 xxx B表
列名1 列名2
s1 1
s2 1
s3 1 declare @test as varchar(100)
set @test=""
select 列名1,(select @test =@test +'\'from A left join B where A.列名1=B.列名2)
from B