加入逗号分隔的数据列 [英] join comma delimited data column

问题描述

我的 table1 是:

my table1 is :

col1    col2
 C1     john
 C2     alex
 C3     piers
 C4     sara

等等表2:

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，这样您就不会存储逗号分隔的列表.

Ideally, your best solution would be to normalize Table2 so you are not storing a comma separated list.

一旦您将这些数据标准化，您就可以轻松查询数据.新的表结构可能类似于:

Once you have this data normalized then you can easily query the data. The new table structure could be similar to this:

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')
;

对表进行标准化将使您通过联接表来查询数据变得更加容易:

Normalizing the tables would make it much easier for you to query the data by joining the tables:

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

参见演示

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

Then if you wanted to display the data as a comma-separated list, you could use 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;

参见演示.

如果您无法对数据进行规范化，那么您可以做几件事.

If you are not able to normalize the data, then there are several things that you can do.

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

First, you could create a split function that will convert the data stored in the list into rows that can be joined on. The split function would be similar to this:

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, 函数时，您可以将数据保留在多行中，也可以将值连接回逗号分隔列表:

When you use the split, function you can either leave the data in the multiple rows or you can concatenate the values back into a comma separated list:

;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

参见演示.

获得结果的最后一种方法是直接应用 FOR XML PATH.

A final way that you could get the result is by applying FOR XML PATH directly.

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;

参见SQL Fiddle with Demo

这篇关于加入逗号分隔的数据列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！