title: Displaying comma separated values from table
body: <p>I have asked question on same schema before. This time my requirement is to take output from the table. For schema, please refer this <strong><a href="https://stackoverflow.com/questions/6596777/getting-count-from-sql-tables.">question</a></strong></p>
  
  <p>Now what i want to query the company Id from map table and display all sub categories of a category as a Comma Separated Value. Scenario is something like tht. 
  If i am logged in as company Id 1. and I have 3 different categories. Then in my grid, it will show:</p>
  
  <pre><code>**Category**                                   **Subcategory**
  
  Category1                SubCat1, Subcat2, Subcat3 and so on
  Category2                SubCat4, Subcat62, Subcat6 and so on
  </code></pre>
  
  <p>My Existing query is this:</p>
  
  <pre><code>    SELECT tbl_Category.Name , dbo.fnGetCSValues(@CompanyId) AS Subcategory
        FROM tbl_Company_Category_Map 
    INNER JOIN tbl_Category ON tbl_Company_Category_Map.CategoryId = tbl_Category.Id 
    INNER JOIN tbl_SubCategory ON tbl_Company_Category_Map.SubCategoryId = tbl_SubCategory.Id
       WHERE tbl_Company_Category_Map.CompanyId = @CompanyId
      GROUP BY tbl_Category.Name
  </code></pre>
  
  <p>With a UDF </p>
  
  <pre><code>ALTER FUNCTION [dbo].[fnGetCSValues] (@Id INT)
  
  RETURNS VARCHAR(8000)
  AS
  BEGIN
  
   DECLARE @List VARCHAR(8000)
   SELECT @List =(SELECT tbl_SubCategory.Name    
                  FROM      tbl_Company_Category_Map INNER JOIN
                            tbl_Category ON tbl_Company_Category_Map.CategoryId = tbl_Category.Id INNER JOIN
                            tbl_SubCategory ON tbl_Company_Category_Map.SubCategoryId = tbl_SubCategory.Id
                  WHERE tbl_Company_Category_Map.CompanyId = @Id
                  ORDER by tbl_SubCategory.Name 
  
                   FOR    XML PATH(''))
   RETURN LEFT(@List,(LEN(@List) -1))
  END
  </code></pre>