选择回按 ID 分组的逗号分隔列表 [英] Select back a comma delimited list grouped by an ID
问题描述
我有以下表格:
EntryTag
---------
EntryID
TagID
示例输入(EntryID、TagID):
Example putput (EntryID, TagID):
1 2
1 4
1 5
2 3
2 4
2 5
etc...
和
Tags
----
TagID
Name
示例输出:
1 peas
2 corn
3 carrots
...etc.
我想带回每个条目的标签列表，但作为标签以逗号分隔的一行.
I want to bring back the list of tags per entry but as one line where tags are comma delimited.
例如我想看这个:
EntryID TagsCommaDelimited
------- ------------------
1 corn, peas, carrots
2 barley, oats
...and so on
所以我需要列出每个 EntryID 及其对应的以逗号分隔的标签列表.
So I need to list each EntryID and it's corresponding list of tags comma delimited.
我从内容表中选择，如下所示:
And I'm select form the Content table which looks like this:
Content
--------
ID -(which is in essence the EntryID, they didn't make it consistent)
Description
..etc.
这是我尝试过的，但我的语法不走运:
Here's what I've tried, but no luck with my syntax:
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 进行测试
for now I went with gotgn for testing
我现在遇到的问题是，我试图在我的最终选择语句中使用最后一个 CTE 来获取以逗号分隔的标签名称列表......但它说我的语法不正确:
The problem I have now is, I'm trying to use that last CTE in my final select statement to grab the comma delimited list of tag names..but it's saying my syntax is not right:
;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
好吧，我猜你不能加入，我不得不把它改成 Where.不知道为什么，但这现在有效:
ok I guess you can't have a join, I had to change it to Where. NOt sure why, but this works now:
Select
....other fields
(select CommaDelimitedTagNames from CommaDelimittedTagNames_CTE tagNames
where Content.ID = tagNames.EntryID) as tags
FROM Content
推荐答案
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 只能获得一行.
The main query does a group by so you only get one row for each EntryID.
列 TagsCommaDelimited 是使用相关子查询创建的.
The column TagsCommaDelimited is created with a correlated subquery.
在 SQL Server for xml path 用于创建查询结果的 XML 表示.通过使用列别名和 path 和 root 的参数，您可以很好地控制如何创建 XML.
In SQL Server for xml path is used to create a XML representation of a query result. You have good control over how the XML is created by using column aliases and the parameters to path and root.
关联子查询中的连接值', '+T.Name 将没有列名，并且for xml path('') 创建的空参数根本没有任何标签的 xml.将只返回一个文本值.
The concatenated value ', '+T.Name in the corelated subquery will not have a column name and the empty parameter to for xml path('') creates the xml without any tags at all. There will be only one text value returned.
当您将 type 添加到for xml 查询的数据类型将是 XML.
When you add type to a for xml query the data type will be XML.
要从 XML 中获取值，您应该使用 值() 方法.您可以转换为字符串，但如果您这样做了，例如，您将在字符串中使用 & 获得 &.
To get a value out of a XML you should use the value() method. You could cast to a string but if you did that you would for instance get & in the string wherever you have used &.
value() 函数中的第一个参数是用于获取所需值的 xQuery 表达式.使用 text() 指定您只需要当前元素的值.[1] 告诉 SQL Server 你想要找到第一个文本节点(你这里只有一个)，但它仍然是必要的.
The first parameter in the value() function is the xQuery expression used to get the value you want. Use text() to specify that you only want the value for the current element. [1] is telling SQL Server that you want the first text node found (you only have one here) but it is still necessary.
由 for xml 查询创建的字符串在字符串的开头有一个额外的逗号和一个空格，需要删除.这里我使用 XQuery 函数 substring获取除前两个字符以外的所有内容.
The string created by the for xml query has an extra comma and a space at the beginning of the string and that needs to be removed. Here I use the XQuery function substring to get everything but the first two characters.
value() 的第二个参数指定应返回的数据类型.
The second parameter to value() specifies the datatype that should be returned.
这篇关于选择回按 ID 分组的逗号分隔列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
