title: SQL Server 2012 Concatenating Strings from Multiple Rows
body: <p>[Edit] Due to time constraints I gave up on using a CTE and created a function that returns the concatenated string:</p>
  
  <pre><code>CREATE FUNCTION fn_GetCategoryNamesAsString
  (
      @lawID INT
  )
  RETURNS NVARCHAR(MAX)
  AS
  BEGIN
  
      DECLARE @categoryNames NVARCHAR(MAX)
      SET @categoryNames = ''
  
      DECLARE @categoryID INT
  
      DECLARE CUR CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR 
          SELECT t1.LawCategoryID FROM [GWS].[dbo].[GWSMasterLawsLawCategories] t1 WHERE t1.LawID = @lawID
      OPEN CUR
  
      FETCH FROM CUR INTO @categoryID
      WHILE @@FETCH_STATUS = 0
      BEGIN
          SET @categoryNames = @categoryNames + (SELECT t2.Name
                                                          FROM [GWS].[dbo].GWSMasterLawCategories t2
                                                          WHERE t2.LawCategoryID = @categoryID) + ', '
          FETCH NEXT FROM CUR INTO @categoryID
      END
      CLOSE CUR
      DEALLOCATE CUR
  
      RETURN @categoryNames
  
  END
  GO
  </code></pre>
  
  <p>This does the job but I don't really like it. If anyone has a better solution I'd love to know. </p>
  
  <p>[End edit]</p>
  
  <p>I have seen several questions the deal roughly with the same topic but none cover the inclusion of null values.</p>
  
  <p>I am writing a query that should return the full contents of one table with a couple of columns added with relevant data from other tables. These columns can include 0 - n values.</p>
  
  <p>Null values need to be stored as an empty string and sets that do have the extra data should display it separated by commas.</p>
  
  <p>Some approaches delivered all the names strung together, some only returned the values separately, some no values at all and, most often, the recursion went to deep (which means I fouled up as the dataset is small).</p>
  
  <p>This is my current approach:</p>
  
  <pre><code>DECLARE @categoryNames NVARCHAR(MAX);
  SET @categoryNames = '';
  
  WITH sources (sourcesLawSourceID, sourcesName) AS (
      SELECT DISTINCT [LawSourceID], [name]
      FROM [GWS].[dbo].[GWSMasterLawSources]
  ),
  categories AS(
      SELECT GWSCategories.LawCategoryID AS categoryID, GWSLawCategories.LawID AS lawID,
      categoryNames = @categoryNames 
      --CAST(LEFT( GWSCategories.name, CHARINDEX(',', GWSCategories.name + ',') -1)  AS NVARCHAR(MAX)) categoryName,
      --STUFF(GWSCategories.name, 1, CHARINDEX(',', GWSCategories.name + ','), '') categoryNames
      FROM [GWS].[dbo].[GWSMasterLawCategories] GWSCategories
      JOIN [GWS].[dbo].[GWSMasterLawsLawCategories] GWSLawCategories
      ON GWSCategories.LawCategoryID = GWSLawCategories.LawCategoryID
  
      UNION ALL
  
      SELECT categories.categoryID, categories.lawID, 
      CAST(LEFT( @categoryNames, CHARINDEX(',', @categoryNames + ',') -1)  AS NVARCHAR(MAX)) + GWSCategories.Name
      FROM categories
      JOIN [GWS].[dbo].[GWSMasterLawCategories] GWSCategories
      ON categories.categoryID = GWSCategories.LawCategoryID
      WHERE @categoryNames &gt; ''
  )
  SELECT DISTINCT GWSMaster.[LawID]
        ,[Name]
  
        ,sources.sourcesName LawSourceName
        ,(SELECT STUFF((SELECT DISTINCT ', ' + RTRIM(LTRIM(categories.CategoryNames))
        FROM categories
        FOR XML PATH ('')), 1, 1, '')) Categories
  
    FROM [GWS].[dbo].[GWSMasterLaws] GWSMaster
    JOIN sources
    ON sources.sourcesLawSourceID = GWSMaster.LawSourceID
    JOIN categories
    ON categories.lawID = GWSMaster.LawID
  </code></pre>
  
  <p>This leaves the category name field completely empty.</p>
  
  <p>If I can give any more information or I have missed a question that answers my problem please let me know. </p>