How to Get Comma Separated values in SQL Server with STUFF and CROSS APPLY keywords

This article describes you how to convert a tabular data into comma separated value.
Here in this example I have 3 tables:
  1. Company - Contains company details
  2. Products - Contains product details
  3. CompanyProduct - Holds the relation between the company and the product. 
I want to generate a result set that provide me a list of all the companies and a list of products separated by comma. Lets check out how to do it.

Table Script 

Company 
  1. CREATE TABLE [dbo].[Company]
  2. (    
  3.     [CompanyId] [int] IDENTITY(1,1) NOT NULL,    
  4.     [CompanyName] [varchar](100) NOT NULL,    

  5.    CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED     
  6.    (    
  7.        [CompanyId] ASC    
  8.    )

  9.    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LO
  10.    CKS = ONON [PRIMARY]    


  11. ON [PRIMARY]    
  12.     
  13. GO   
Product  
  1. CREATE TABLE [dbo].[Product]
  2. (    
  3.     [ProductId] [int] IDENTITY(1,1) NOT NULL,    
  4.     [ProductName] [varchar](100) NOT NULL,    
  5.    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED     
  6.    (    
  7.        [ProductId] ASC    
  8.    )
  9.    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_L
  10.    OCKS = ONON [PRIMARY]    

  11.    ON [PRIMARY]    
  12.     
  13. GO  

Company Product
  1. CREATE TABLE [dbo].[CompanyProduct](    
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,    
  3.     [CompanyId] [intNOT NULL,    
  4.     [ProductId] [intNOT NULL,    
  5.  CONSTRAINT [PK_CompanyProduct] PRIMARY KEY CLUSTERED     
  6. (    
  7.     [Id] ASC    
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]    
  9. ON [PRIMARY]    
  10.     
  11. GO    
  12.     
  13. ALTER TABLE [dbo].[CompanyProduct]  WITH CHECK ADD  CONSTRAINT [FK_CompanyProduct_Company] FOREIGN KEY([CompanyId])    
  14. REFERENCES [dbo].[Company] ([CompanyId])    
  15. GO    
  16.     
  17. ALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT [FK_CompanyProduct_Company]    
  18. GO    
  19.     
  20. ALTER TABLE [dbo].[CompanyProduct]  WITH CHECK ADD  CONSTRAINT [FK_CompanyProduct_Product] FOREIGN KEY([ProductId])    
  21. REFERENCES [dbo].[Product] ([ProductId])    
  22. GO    
  23.     
  24. ALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT [FK_CompanyProduct_Product]    
  25. GO  
Table Data
 
 
 
I want to have my result set look like this.
 
 
 
I have accomplished this task using STUFF and CROSS APPLY keyword. The query is as mentioned below:
  1. SELECT C.CompanyName, X.ProductList FROM     
  2. dbo.[Company] C     
  3. CROSS APPLY    
  4. (    
  5. SELECT STUFF    
  6.             (    
  7.                 (    
  8.                     SELECT ',' + P.ProductName FROM dbo.[Product] AS P    
  9.                     INNER JOIN dbo.[CompanyProduct] AS CP ON P.ProductId=CP.ProductId     
  10.                     WHERE CP.CompanyId=C.CompanyId    
  11.                     FOR XML PATH('')    
  12.                 )    
  13.                     
  14.             ,1,1,''as ProductList    
  15. as X    
Happy Coding