加入逗号分隔的数据列(join comma delimited data column)

2019-09-02 17:27发布

站内文章 / 前端开发
57 0 0

我的表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

请帮我？

Answer 1:

理想情况下，最好的解决办法是规范表2这样你就不会存储逗号分隔的列表。

一旦你有了这个数据归一化，那么你可以很容易地查询数据。 新的表结构可能类似于此： 

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

见演示

然后，如果你想显示的数据作为逗号分隔的列表，你可以使用FOR XML PATH和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;

见演示 。

如果你不能够以标准化的数据，然后有几件事情，你可以做。

首先，你可以创建一个分裂的功能，将存储在列表中的数据转换成可以被加入了行。 分割功能将类似于此： 

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;

当您使用拆分，功能，您可以保留数据的多行或者您可以连接值回一个逗号分隔的列表： 

;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直接。 

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拨弄演示



Answer 2:

这里的分割数据没有的功能，然后用标准的方式XML PATH方法用于获取CSV列表： 

with CTE as
(
  select T2.col1
    , T1.col2
  from T2
    inner join T1 on charindex(',' + T1.col1 + ',', ',' + T2.col2 + ',') > 0
)
select T2.col1
  , col2 = stuff(
      (
        select ',' + CTE.col2
        from CTE
        where T2.col1 = CTE.col1
        for xml path('')
      )
      , 1
      , 1
      , ''
    )
from T2

SQL拨弄演示 。

正如已经在这个问题提到其他地方很难查询这种去归一化数据的任何形式的有效的方式，让你的首要任务应该是调查更新表结构，但是这将至少允许获得您所需要的结果。



Answer 3:

如果你想要做这个任务在Oracle中，我们可以使用listagg并可以很容易地做到这一点。

好吧，我不是在SQL SERVER不错，但我搜索了可能相当于avaialble在sqlserver的为LISTAGG，我得到了功能Stuff为同- 选中此

所以在使用的东西，你可以用下面的查询尝试 - 

SELECT T2.Col1,
       Stuff((SELECT ',' + CAST(T1.Col2 AS VARCHAR(100))
               FROM T1
              WHERE T2.Col2 LIKE T1.Col1
                FOR Xml Path('')),
             1,
             1,
             '')
  FROM T2


Answer 4:

首先写上tbl2的分裂COL2的表值函数。 

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

    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  

Go

;WITH    SplitList
          AS ( SELECT   T2.Col1 ,
                        T1.Col2
               FROM     T2
                        CROSS APPLY dbo.Split(T2.Col2, ',') S
                        INNER JOIN T1 ON T1.Col1 = S.Items
             )
    SELECT  T2.Col1 ,
            STUFF(( SELECT  ', ' + SplitList.Col2
                    FROM    SplitList
                    WHERE   SplitList.Col1 = T2.Col1
                  FOR
                    XML PATH('')
                  ), 1, 2, '')
    FROM    T2


Answer 5:

这个任务不能与标准SQL来解决。 在Oracle我会写一个存储函数（PL / SQL）来解析名称，ID字符串（T2 COL2）和解析名称。 不知道如果在Transact-SQL是可能的，但它是光荣的效率不高。

T2是设计不好的，不归表。 那就是问题所在。 如果你将它标准化，让你有每名-ID（COL 2 T2）一条线，你可以得到的名字用一个简单的列表中加入了两个表。 要生成所需的输出格式（逗号分隔），你需要写的东西比其他SQL - 可能是存储过程或别的东西在结果集进行迭代。



Answer 6:

如果你像我一样，你是CTE的特别递归CTE的是应该的东西，XML路径固执己见： 

DECLARE @T1 TABLE (
    col1 CHAR(2),
    col2 VARCHAR(10)
)
INSERT INTO @T1
VALUES  ('C1', 'john'),
        ('C2', 'alex'),
        ('C3', 'piers'),
        ('C4', 'sara');

DECLARE @T2 TABLE (
    col1 CHAR(2),
    col2 CHAR(100)
)
INSERT INTO @T2
VALUES  ('R1', 'C1,C2,C4'),
        ('R2', 'C3,C4'),
        ('R3', 'C1,C4');

WITH T2Sorted AS (
    SELECT col1, col2, RN = ROW_NUMBER() OVER (ORDER BY col1) FROM @T2
), CTERecursionOnT2 AS (
    SELECT RN, col1, col2, 0 AS PrevCharIndex, CHARINDEX(',', col2, 1) AS NextCharIndex FROM T2Sorted
    UNION ALL
    SELECT a.RN, a.col1, a.col2, b.NextCharIndex, CHARINDEX(',', a.col2, b.NextCharIndex + 1) 
    FROM T2Sorted a
    JOIN CTERecursionOnT2 b ON a.RN = b.RN
    WHERE b.NextCharIndex > 0
), CTEIndividualCol2Items AS (
    SELECT *, SUBSTRING(col2, PrevCharIndex + 1, CASE WHEN NextCharIndex = 0 THEN LEN(col2) ELSE NextCharIndex - 1 END - PrevCharIndex) AS itemCol2 
    FROM CTERecursionOnT2
), CTELookupT1 AS (
    SELECT a.col1, b.col2, RN = ROW_NUMBER() OVER (PARTITION BY a.col1 ORDER BY a.PrevCharIndex)
    FROM CTEIndividualCol2Items a
    JOIN @T1 b ON a.itemCol2 = b.col1
), CTERecursionOnLookupT1 AS (
    SELECT col1, CAST(col2 AS VARCHAR(MAX)) AS col2, RN
    FROM CTELookupT1 
    WHERE RN = 1

    UNION ALL

    SELECT a.col1, b.col2 + ',' + a.col2, a.RN
    FROM CTELookupT1 a
    JOIN CTERecursionOnLookupT1 b ON a.col1 = b.col1 AND a.RN = b.RN + 1
), CTEFinal AS (
    SELECT *, RNDesc = ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY RN DESC)
    FROM CTERecursionOnLookupT1
)
SELECT col1, col2
FROM CTEFinal
WHERE RNDesc = 1
ORDER BY col1

很明显，你可以在第一递归部分分解成不同的功能已经同意解决方案建议，即CTERecursionOnT2从而CTEIndividualCol2Items可以是你的替代分割功能（我将包括订单ID为好），因此： 

;WITH CTEIndividualCol2Items AS (
    SELECT a.col1, b.value as itemCol2, b.id AS PrevCharIndex
    FROM @T2 a
    CROSS APPLY (
        SELECT id, items FROM dbo.Split(a.col2, ',')
    ) b
) ...

和拆分功能： 

CREATE FUNCTION dbo.Split(@String varchar(100), @Delimiter char(1))
RETURNS TABLE
AS
RETURN 
(
    WITH CTERecursion AS (
        SELECT id = 1, PrevCharIndex = 0, NextCharIndex = CHARINDEX(@Delimiter, @String, 1)
        UNION ALL
        SELECT id + 1, NextCharIndex, CHARINDEX(@Delimiter, @String, NextCharIndex + 1) FROM CTERecursion WHERE NextCharIndex > 0
    )
    SELECT Id, items = SUBSTRING(@String, PrevCharindex + 1, (CASE WHEN NextCharIndex = 0 THEN LEN(@String) ELSE NextCharIndex - 1 END) - PrevCharIndex)
    FROM CTERecursion
    WHERE @String > ''
)


文章来源: join comma delimited data column
标签： sql sql-server tsql
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~