选择回来的ID分组逗号分隔的列表(Select back a comma delimited lis

我有如下表：

EntryTag
---------
EntryID
TagID

实施例putput（ENTRYID，标签识别）：

和

Tags
----
TagID
Name

示例输出：

1 peas
2 corn
3 carrots
...etc.

我想带回的每个条目，但作为一个行，其中标签逗号分隔的标记列表。

例如，我想看到这一点：

EntryID     TagsCommaDelimited
-------     ------------------
1           corn, peas, carrots
2           barley, oats
...and so on

所以，我需要列出每个的EntryID和它对应的逗号分隔的标签列表。

而我选择的形式，看起来像这样的内容表：

Content
--------
ID   -(which is in essence the EntryID, they didn't make it consistent)
Description
..etc.

下面是我尝试过，但我的语法没有运气：

declare @tagsCommaDelimited varchar (200) 
set @tagsCommaDelimited = '';


With AllEntryTags_CTE(Name, EntryID )
as
(
    select Tags .Name,
           entryTags.EntryID 
    from  EntryTag entryTags
    join Tags on tags.Id = entryTags.TagID
    group by entryTags.EntryID, tags.Name, entryTags.TagID 
),

TagsByEntryCommaDelimited_CTE( EntryID, CommaDelimitedTags)
as
(
    select  distinct allTags.EntryID,
            (select @tagsCommaDelimited from ( select @tagsCommaDelimited = coalesce (case when @tagsCommaDelimited = '' then allTags.Name
                                                                                      else @tagsCommaDelimited + ',' + allTags.Name end ,'') as CommaDelimitedTags
    from  AllEntryTags_CTE allTags  
)

select EntryID, CommaDelimitedTags from TagsByEntryCommaDelimited_CTE

--------------------------- UPDATE ---------------------- ------------

现在我去gotgn测试

我现在的问题是，我想用这最后CTE在我最后的select语句抢逗号标签分隔的列表names..but它在说我的语法是不正确的：

;WITH CommaDelimitedTagIDs AS
(
    SELECT DISTINCT EntryID,
    (SELECT SUBSTRING((SELECT ',' + CAST(TagID AS NVARCHAR(10)) 
                       FROM EntryTag AS T1 WHERE T1.EntryID=T2.EntryID 
                       ORDER BY TagID 
                       FOR XML PATH('')),2,200)) AS commaDelimitedTagIDs
    FROM EntryTag T2 
),

CommaDelimittedTagNames_CTE (EntryID, CommaDelimitedTagNames) as
( 
    SELECT EntryID, (SELECT SUBSTRING((SELECT ',' + Name 
                     FROM Tags 
                     WHERE commaDelimitedTagIDs LIKE '%'+CAST(ID AS NVARCHAR(5))+'%'  
                     ORDER BY ID FOR XML PATH('')),2,200) AS CSV) 
    FROM CommaDelimitedTagIDs
)

--select EntryID, CommaDelimitedTagNames from CommaDelimittedTagNames_CTE


SELECT  Title,
        [Description],
        DateSyndicated,
        DateUpdated,
        1, 
        CAST([Text] AS NVARCHAR(MAX)),
        Author,
        (select CommaDelimitedTagNames from CommaDelimittedTagNames_CTE) as tagNamesCommaDelimited 
 FROM Content
 Join CommaDelimittedTagNames_CTE tags on tags.EntryID = Content.ID
 group by ID, Title, [Description], 
          DateSyndicated, DateUpdated, 
          CAST(subtextContent.[Text] AS NVARCHAR(MAX)), Author

尝试过这种方式也没有运气

  Select
    ....other fields
    (select CommaDelimitedTagNames from CommaDelimittedTagNames_CTE tagNames 
     join subContent on subContent.ID = tagNames.EntryID) as tags 
  FROM Content as subContent

好，我想你不能有一个连接，我不得不改变它到哪里。不知道为什么，但这个工程现在：

   Select
    ....other fields
    (select CommaDelimitedTagNames from CommaDelimittedTagNames_CTE tagNames 
     where Content.ID = tagNames.EntryID) as tags 
  FROM Content

Answer 1:

select ET1.EntryID,
       (
       select ', '+T.Name
       from Tags as T
         inner join EntryTag as ET2
           on T.TagID = ET2.TagID
       where ET1.EntryID = ET2.EntryID
       for xml path(''), type
       ).value('substring(text()[1], 3)', 'varchar(max)') as TagsCommaDelimited
from EntryTag as ET1
group by ET1.EntryID

剖析查询

主查询做了一个group by ，所以你只能得到每一行对应一个EntryID 。

该柱TagsCommaDelimited与相关子查询创建的。

在SQL Server中for xml path被用来创建一个查询结果的XML表示。你有超过怎样XML是通过使用列别名和参数，创造了良好的控制path和root 。

合并的值', '+T.Name在corelated子查询不会有列名和空参数for xml path('')创建无可言任何标签的XML。只会有一个文本值返回。

当您添加type的for xml查询的数据类型为XML 。

为了得到一个值了，你应该使用一个XML的value()方法。你可以转换为字符串，但如果你这样做，你会例如获得& 在字符串中，无论你已经使用& 。

在第一个参数value()函数是用来获取你想要的值XQuery表达式。使用text()以指定您只想为当前元素的值。 [1]告诉你想要找到的第一个文本节点的SQL Server（你只有一个在这里），但它仍然是必要的。

由创建的字符串for xml查询有一个额外的逗号和在字符串的开头的空间和需要被去除。在这里，我使用XQuery函数substring得到的一切，但前两个字符。

的第二个参数value()指定应返回的数据类型。

Answer 2:

DECLARE @TableOne TABLE
(
    EntryID INT,
    TagID INT
)

DECLARE @TableTwo TABLE
(
    TagID INT,
    Name NVARCHAR(100)
)

INSERT INTO @TableOne (EntryID,TagID)
VALUES  (1,2)
       ,(1,4)
       ,(1,5)
       ,(2,3)
       ,(2,4)
       ,(2,1)

INSERT INTO @TableTwo (TagID,Name)
VALUES  (1,'Daniel')
       ,(2,'Samuel')
       ,(3,'Petkov')
       ,(4,'Ivan')
       ,(5,'Jack')

/* 
    In this CTE we are going to format the values int the folowing way:

    1   2,4,5
    2   1,3,4

    Or for eaech EntryIDs, we will have all its TagIDs 

*/
;WITH CTE AS
(
    SELECT DISTINCT EntryID
          ,(SELECT SUBSTRING((SELECT ',' + CAST(TagID AS NVARCHAR(10)) FROM @TableOne AS T1 WHERE T1.EntryID=T2.EntryID ORDER BY TagID FOR XML PATH('')),2,200)) AS CSVTags
    FROM @TableOne T2

)
/*
    Here we are replacing the EntryIDs with their names from the @TableTwo:
*/
SELECT EntryID
      ,(SELECT SUBSTRING((SELECT ',' + Name FROM @TableTwo WHERE CSVTags LIKE '%'+CAST(TagID AS NVARCHAR(5))+'%' ORDER BY TagID FOR XML PATH('')),2,200) AS CSV) 
FROM CTE

文章来源: Select back a comma delimited list grouped by an ID