sql 查询替换带逗号的字段

[Dotar]

表A
id name
1 a
2 b
3 c
4 d

表b
id expand
1 1,2
2 2,3,4
3 1,3,4

我要查询出来的结果替换表b中的内容
查询结果如下：
id expand
1 a,b
2 b,c,d
3 a,c,d

[二月十六]

新建函数

CREATE FUNCTION dbo.F_Split
 (
     @SplitString nvarchar(max),  --源字符串
     @Separator nvarchar(10)=' '  --分隔符号，默认为空格
 )
 RETURNS @SplitStringsTable TABLE  --输出的数据表
 (
     [id] int identity(1,1),
     [value] nvarchar(max)
 )
 AS
 BEGIN
     DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     DECLARE @ReturnText nvarchar(max);

     SELECT @CurrentIndex=1;
     WHILE(@CurrentIndex<=len(@SplitString))
         BEGIN
             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
             IF(@NextIndex=0 OR @NextIndex IS NULL)
                 SELECT @NextIndex=len(@SplitString)+1;
                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
                 SELECT @CurrentIndex=@NextIndex+1;
             END
     RETURN;
 END
GO

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] int,[name] nvarchar(21))
Insert #A
select 1,N'a' union all
select 2,N'b' union all
select 3,N'c' union all
select 4,N'd'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([id] int,[expand] nvarchar(25))
Insert #B
select 1,N'1,2' union all
select 2,N'2,3,4' union all
select 3,N'1,3,4'
Go
--测试数据结束
SELECT  id ,
        STUFF(( SELECT  ',' + name
                FROM    #A
                        JOIN dbo.F_Split(expand, ',') t ON t.value = #A.id
              FOR
                XML PATH('')
              ), 1, 1, '') AS expand
FROM    #B

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
CREATE TABLE A(id INT,[name] VARCHAR(100))
CREATE TABLE B(id INT,[expand] VARCHAR(100))
SET NOCOUNT ON;
INSERT INTO A VALUES (1,'a')
INSERT INTO A VALUES (2,'b')
INSERT INTO A VALUES (3,'c')
INSERT INTO A VALUES (4,'d')
INSERT INTO B VALUES (1,'1,2')
INSERT INTO B VALUES (2,'2,3,4')
INSERT INTO B VALUES (3,'1,3,4')

SELECT b.id,
STUFF(
	(
		SELECT ','+a.[name]
		FROM  (
				SELECT [value] = CONVERT(XML, '<v>' + REPLACE(B.[expand], ',', '</v><v>') + '</v>')  
			   ) aa  
			   OUTER APPLY(  
			SELECT id = N.v.value('.', 'nvarchar(max)')  
			FROM   aa.[value].nodes('/v') N(v)  
		) bb
		INNER JOIN A ON bb.id=a.id
		WHERE ISNULL(B.id,'')!=''
		FOR XML PATH('')	
	),1,1,'') AS expandName
FROM B
/*
id	expandName
1	a,b
2	b,c,d
3	a,c,d
*/

[文盲老顾]

with a as (
	select 1 as id,'a' as name
	union all
	select 2,'b'
	union all
	select 3,'c'
	union all
	select 4,'d'
),b as (
	select 1 as id,'1,2' as expand
	union all
	select 2,'2,3,4'
	union all
	select 3,'1,3,4'
)
select * from b
cross apply(
	select stuff((select ','+name from a where charindex(','+convert(varchar(max),id)+',',','+b.expand+',')>0 for xml path('')),1,1,'') as ex
) c