连接逗号分隔的数据列

我的表1是：

T1

col1    col2
 C1     john
 C2     alex
 C3     piers
 C4     sara

表2：

T2

col1    col2
 R1     C1,C2,C4
 R2     C3,C4
 R3     C1,C4

如何产生这个结果？：

查询结果

col1      col2
 R1       john,alex,sara
 R2       piers,sara
 R3       john,sara

请帮我？

理想情况下，最好的解决方案是标准化 Table2，这样您就不会存储逗号分隔的列表。

一旦您对这些数据进行标准化，您就可以轻松查询数据。新的表结构可能类似于：

CREATE TABLE T1
(
  [col1] varchar(2), 
  [col2] varchar(5),
  constraint pk1_t1 primary key (col1)
);

INSERT INTO T1
    ([col1], [col2])
VALUES
    ('C1', 'john'),
    ('C2', 'alex'),
    ('C3', 'piers'),
    ('C4', 'sara')
;

CREATE TABLE T2
(
  [col1] varchar(2), 
  [col2] varchar(2),
  constraint pk1_t2 primary key (col1, col2),
  constraint fk1_col2 foreign key (col2) references t1 (col1)
);

INSERT INTO T2
    ([col1], [col2])
VALUES
    ('R1', 'C1'),
    ('R1', 'C2'),
    ('R1', 'C4'),
    ('R2', 'C3'),
    ('R2', 'C4'),
    ('R3', 'C1'),
    ('R3', 'C4')
;

规范化表将使您更容易通过连接表来查询数据：

select t2.col1, t1.col2
from t2
inner join t1
  on t2.col2 = t1.col1

See Demo

然后，如果您想将数据显示为逗号分隔的列表，您可以使用FOR XML PATH and STUFF:

select distinct t2.col1, 
  STUFF(
         (SELECT distinct ', ' + t1.col2
          FROM t1
          inner join t2 t
            on t1.col1 = t.col2
          where t2.col1 = t.col1
          FOR XML PATH ('')), 1, 1, '') col2
from t2;

See Demo.

如果您无法标准化数据，那么您可以采取多种措施。

首先，您可以创建一个拆分函数，它将存储在列表中的数据转换为可以连接的行。 split 函数类似于：

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

当您使用 split, 函数时，您可以将数据保留在多行中，也可以将值连接回逗号分隔的列表中：

;with cte as
(
  select c.col1, t1.col2
  from t1
  inner join 
  (
    select t2.col1, i.items col2
    from t2
    cross apply dbo.split(t2.col2, ',') i
  ) c
    on t1.col1 = c.col2
) 
select distinct c.col1, 
  STUFF(
         (SELECT distinct ', ' + c1.col2
          FROM cte c1
          where c.col1 = c1.col1
          FOR XML PATH ('')), 1, 1, '') col2
from cte c

See Demo.

获得结果的最后一种方法是申请FOR XML PATH直接地。

select col1, 
(
  select ', '+t1.col2
  from t1
  where ','+t2.col2+',' like '%,'+cast(t1.col1 as varchar(10))+',%'
  for xml path(''), type
).value('substring(text()[1], 3)', 'varchar(max)') as col2
from t2;

See SQL 摆弄演示