求一条组合字符串的SQL语句

Question

有表1:
ID    Name
1     a
2     b
4     c
5     d
8     e
9     f表2:
ID       Value
1        8,9,1,2,4,5
2        8,9,2,4要得出这样的结果：
ID       Value               Result    
1        8,9,1,2,4,5        e,f,a,b,c,d
2        8,9,2,4             e,f,b,c即：表2中的Values是由表1中的ID用逗号连接起来的，
Result结果值就是：把逗号连接起来的ID值转换成其对应的NameSQL语句

Answer 1

----------------------------------------------------------------
-- Author  :DBA_Huanzj（發糞塗牆）-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
-- Oct 19 2012 13:38:57 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[表1]
if object_id('[表1]') is not null drop table [表1]
go 
create table [表1]([ID] int,[Name] varchar(1))
insert [表1]
select 1,'a' union all
select 2,'b' union all
select 4,'c' union all
select 5,'d' union all
select 8,'e' union all
select 9,'f'--> 测试数据：[表2]
if object_id('[表2]') is not null drop table [表2]
go 
create table [表2]([ID] int,[Value] varchar(11))
insert [表2]
select 1,'8,9,1,2,4,5' union all
select 2,'8,9,2,4'
--------------开始查询--------------------------;with cte as (
select b.id,a.Name,b.Value
from [表1] a inner join (
select
    id, 
    SUBSTRING([Value],number,CHARINDEX(',',[Value]+',',number)-number) as [Value] 
from
    [表2] a,master..spt_values 
where
    number >=1 and number<len([Value])  
    and type='p'
    and substring(','+[Value],number,1)=',') b on a.id=b.value)select a.id,
stuff((select ','+[name] from cte b 
       where b.id=a.id 
       for xml path('')),1,1,'') 'Name',
stuff((select ','+[Value] from cte b 
       where b.id=a.id 
       for xml path('')),1,1,'') 'Value'
from cte a
group by  a.id----------------结果----------------------------
/* 
id          Name                                                                                                                                                                                                                                                             Value
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           e,f,a,b,c                                                                                                                                                                                                                                                        8,9,1,2,4
2           e,f,b      
*/

Answer 2

declare @t1 table([ID] int,[Name] varchar(1))
insert @t1
select 1,'a' union all
select 2,'b' union all
select 4,'c' union all
select 5,'d' union all
select 8,'e' union all
select 9,'f'declare @t2 table([ID] int,[Value] varchar(11))
insert @t2
select 1,'8,9,1,2,4,5' union all
select 2,'8,9,2,4';with maco as
(
select t.id,t.value,c.Name from 
(
select A.id, B.value FROM
( 
select id, [value] = convert(xml,' <root> <v>' + replace([value], ',', ' </v> <v>') + ' </v> </root>') from @t2 
)A 
outer APPLY
( 
select value = N.v.value('.', 'varchar(100)') from A.[value].nodes('/root/v') N(v) 
)B 
) t 
left join @t1 c on t.value=c.ID
)select 
id,  
[value] =stuff((select ','+ value  from maco where id=t.id for xml path('')), 1, 1, '') ,
Result =stuff((select ','+ Name  from maco where id=t.id for xml path('')), 1, 1, '') 
from maco t group by id /*
id          value              Result
----------- ------------------ -----------------
1           8 ,9 ,1 ,2 ,4 ,5   e,f,a,b,c,d
2           8 ,9 ,2 ,4         e,f,b,c
*/

Answer 3

--创建自定义函数：
CREATE   FUNCTION [dbo].[f_split]
    (
      @c VARCHAR(MAX) ,
      @split VARCHAR(2)
    )
RETURNS @t TABLE ( col VARCHAR(20) )
AS 
    BEGIN   
    
        WHILE ( CHARINDEX(@split, @c) <> 0 ) 
            BEGIN   
                INSERT  @t
                        ( col
                        )
                VALUES  ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1)
                        )   
                SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')   
            END   
        INSERT  @t
                ( col )
        VALUES  ( @c )   
        RETURN   
    END   
GO--查询代码：
IF object_id('tempdb..#a1') is not null
DROP table #a1;
IF object_id('tempdb..#a2') is not null
DROP table #a2;
IF object_id('tempdb..#a3') is not null
DROP table #a3;WITH a (ID,Name) as
(
select 1,'a' UNION all
select 2,'b' UNION all
select 4,'c' UNION all
select 5,'d' UNION all
select 8,'e' UNION all
select 9,'f'
)
SELECT * INTO #a1 FROM a;
WITH a (ID,Value) as
(
select 1,'8,9,1,2,4,5' UNION all
select 2,'8,9,2,4'
)
SELECT * INTO #a2 FROM aSELECT TOP 0 id,id id2 INTO #a3 FROM #a1DECLARE @i1 INT,@i2 INT
SELECT @i1=1,@i2=MAX(id) FROM #a2
WHILE @i1<=@i2
BEGIN
INSERT INTO #a3
select @i1,* 
from dbo.f_split((SELECT Value FROM #a2 WHERE id=@i1),',')
SET @i1=@i1+1
END;
WITH a1 AS
(
SELECT a.*,b.Name
,ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY GETDATE()) re
FROM #a3 a
INNER JOIN #a1 b ON a.id2=b.ID
)
SELECT id
,value=STUFF((SELECT ','+RTRIM(id2) FROM a1 WHERE id=a.id ORDER BY re for xml path('')),1,1,'')
,result=STUFF((SELECT ','+RTRIM(name) FROM a1 WHERE id=a.id ORDER BY re for xml path('')),1,1,'')
FROM a1 a
GROUP BY id

Answer 4

--查询代码简化(现学与卖)：
IF object_id('tempdb..#a1') is not null
DROP table #a1;
IF object_id('tempdb..#a2') is not null
DROP table #a2;WITH a (ID,Name) as
(
select 1,'a' UNION all
select 2,'b' UNION all
select 4,'c' UNION all
select 5,'d' UNION all
select 8,'e' UNION all
select 9,'f'
)
SELECT * INTO #a1 FROM a;
WITH a (ID,Value) as
(
select 1,'8,9,1,2,4,5' UNION all
select 2,'8,9,2,4'
)
SELECT * INTO #a2 FROM a;
WITH a1 AS
(
SELECT a.*,b.name
,ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY GETDATE()) re
from
(
SELECT *
FROM #a2 a
cross APPLY
(
select id2=col
from dbo.f_split((SELECT Value FROM #a2 WHERE id=a.id),',')
) b
) a
INNER JOIN #a1 b ON a.id2=b.id
)SELECT id
,value
,result=STUFF((SELECT ','+RTRIM(name) FROM a1 WHERE id=a.id ORDER BY re for xml path('')),1,1,'')
FROM a1 a
GROUP BY id,value