if object_id('[A]') is not null drop table [A]
go
create table [A] (id varchar(8),sid varchar(8))
insert into [A]
select '1','1,2,3' union all
select '2','2,3'
if object_id('[B]') is not null drop table [B]
go
create table [B] (id varchar(8),name nvarchar(8))
insert into [B]
select '1','张' union all
select '2','李' union all
select '3','王'
select id=a.id+','+b.id+','+c.id,name=a.name+','+b.name+','+c.name into #temp20131216
from [B] a
cross join [B] b
cross join [B] c
select a.id,b.name
from [a] a
inner join #temp20131216 b on a.sid=b.id
create table ta(id int,ids varchar(100))
insert into ta
select '1','1,2,3'
union all select '2','2,3'
create table tb(id int,name varchar(10))
insert into tb
select '1','张'
union all select '2','李'
union all select '3','王'
go
create function dbo.fn_name(@ids varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+','+[name]
from tb where charindex(','+cast(id as varchar)+',',','+@ids+',')>0
return (stuff(@s,1,1,''))
end
go
select id,name=dbo.fn_name(ids) from ta
drop table ta,tb
drop function dbo.fn_name
/*
id name
----------------------
1 张,李,王
2 李,王
*/
if object_id('[A]') is not null drop table [A]
go
create table [A] (id varchar(8),sid varchar(8))
insert into [A]
select '1','1,2,3' union all
select '2','2,3'
if object_id('[B]') is not null drop table [B]
go
create table [B] (id varchar(8),name nvarchar(8))
insert into [B]
select '1','张' union all
select '2','李' union all
select '3','王'
select a.id,b.namelist
from [A] a
inner join
(select a.*,namelist=stuff((
select ','+name
from [B]
where 1=1
for xml path('')),1,1,'')
from [B] a
)b on a.id=b.id
where len(sid)-len(replace(sid,',',''))=2
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-17 07:32:32
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[sId] varchar(5))
insert [A]
select 1,'1,2,3' union all
select 2,'2,3'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[name] varchar(2))
insert [B]
select 1,'张' union all
select 2,'李' union all
select 3,'王'
--------------开始查询--------------------------
;WITH ym AS (
SELECT a.id,b.name
FROM (
select
id,
SUBSTRING(sid,number,CHARINDEX(',',sid+',',number)-number) as sid
from
[A] a,master..spt_values
where
number >=1 and number<=len(sid)
and type='p'
and substring(','+sid,number,1)=',')a INNER JOIN b ON a.sid=b.id)
select a.id,
stuff((select ','+name from ym b
where b.id=a.id
for xml path('')),1,1,'') 'name'
from ym a
group by a.id
----------------结果----------------------------
/*
id name
----------- ------------
1 张,李,王
2 李,王
*/
if object_id('tempdb..#a')is not null
drop table #a
go
create table #a
(
id int,
sid varchar(50)
)
insert into #a
select 1,'1,2,3' union all
select 2,'2,3'
if object_id('tempdb..#b')is not null
drop table #b
go
create table #b
(
id int,
name varchar(50)
)
insert into #b
select 1,'张' union all
select 2,'李' union all
select 3,'王'
------查询语句如下
declare @ids varchar(100)
declare @ss varchar(2000)
declare @id varchar (2)
set @id=1 --传入进来要查询的条件
select @ids=sid from #a where id =@id
set @ss='declare @sql varchar(500) ; select @sql= isnull(@sql+'','','''')+ name from #b where id in ('+@ids+')';
set @ss =@ss+';select a.id , @sql as name from #a a where id='+@id;
exec( @ss)
--------结果
1 张,李,王
if exists(select 1 from sys.tables where name='a')
drop table a
create table a(id int,sid varchar(200))
insert into a select 1,'1,2,3' union all select 2,'2,3'
go
if exists(select 1 from sys.tables where name='b')
drop table b
create table b(id int,name varchar(200))
insert into b select 1,'张' union all select 2,'李' union all select 3,'王'
go
if exists(select 1 from sys.procedures where name='proc_a')
drop proc proc_a
go
create proc proc_a
as
begin
declare @a int
declare @b varchar(200)
set @a=1
while @a<=(select MAX(id)+1 from b)
begin
select @b=name from b where id=@a
update a set sid=REPLACE(sid,@a,@b)
set @a=@a+1
end
end
go
exec proc_a
go
select * from a
--创建数据：
create table #ta(id int,ids varchar(100))
insert into #ta
select 1,'1,2,3'
union all select 2,'2,3'
create table #tb(id int,name varchar(10))
insert into #tb
select 1,'张'
union all select 2,'李'
union all select 3,'王'
go
--查询语句：
select id,stuff(
(
select ','+name
from #tb
where charindex(rtrim(id),rtrim(a.ids))>0
for xml path('')
),1,1,'')
from #ta a
where id=1
