Select back a comma delimited list grouped by an ID

Question

I've got the following tables:

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

Example putput (EntryID, TagID):

1 2
1 4
1 5
2 3
2 4
2 5
etc...

and

Tags
----
TagID
Name

example output:

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.

For example I want to see this:

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

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----------------------------------

for now I went with gotgn for testing

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 

tried it this way also, no luck

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

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 

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

Dissecting the query

The main query does a group by so you only get one row for each EntryID.

The column TagsCommaDelimited is created with a correlated subquery.

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.

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.

When you add type to a for xml query the data type will be 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 &.

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.

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.

The second parameter to value() specifies the datatype that should be returned.