title: Select back a comma delimited list grouped by an ID
body: <p>I've got the following tables:</p>
  
  <pre><code>EntryTag
  ---------
  EntryID
  TagID
  </code></pre>
  
  <p>Example putput (EntryID, TagID):</p>
  
  <pre><code>1 2
  1 4
  1 5
  2 3
  2 4
  2 5
  etc...
  </code></pre>
  
  <p>and</p>
  
  <pre><code>Tags
  ----
  TagID
  Name
  </code></pre>
  
  <p>example output:</p>
  
  <pre><code>1 peas
  2 corn
  3 carrots
  ...etc.
  </code></pre>
  
  <p>I want to bring back the list of tags per entry but as one line where tags are comma delimited. </p>
  
  <p>For example I want to see this:</p>
  
  <pre><code>EntryID     TagsCommaDelimited
  -------     ------------------
  1           corn, peas, carrots
  2           barley, oats
  ...and so on 
  </code></pre>
  
  <p>So I need to list each EntryID and it's corresponding list of tags comma delimited.</p>
  
  <p>And I'm select form the Content table which looks like this:</p>
  
  <pre><code>Content
  --------
  ID   -(which is in essence the EntryID, they didn't make it consistent)
  Description
  ..etc.
  </code></pre>
  
  <p>Here's what I've tried, but no luck with my syntax:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>---------------------------UPDATE----------------------------------</p>
  
  <p>for now I went with gotgn for testing</p>
  
  <p>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:</p>
  
  <pre><code>;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 
  </code></pre>
  
  <p>tried it this way also, no luck</p>
  
  <pre><code>  Select
      ....other fields
      (select CommaDelimitedTagNames from CommaDelimittedTagNames_CTE tagNames 
       join subContent on subContent.ID = tagNames.EntryID) as tags 
    FROM Content as subContent
  </code></pre>
  
  <p>ok I guess you can't have a join, I had to change it to Where.  NOt sure why, but this works now:</p>
  
  <pre><code>   Select
      ....other fields
      (select CommaDelimitedTagNames from CommaDelimittedTagNames_CTE tagNames 
       where Content.ID = tagNames.EntryID) as tags 
    FROM Content 
  </code></pre>