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,

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

  11. WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LO
  12. CKS = ON) ON [PRIMARY]

  14. )

  16. ON [PRIMARY]
  18. 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 = ON) ON [PRIMARY]
  11. )

  13. ON [PRIMARY]
  15. GO

Company Product
  1. CREATE TABLE [dbo].[CompanyProduct](
  2. [Id] [int] IDENTITY(1,1) NOT NULL,
  3. [CompanyId] [int] NOT NULL,
  4. [ProductId] [int] NOT 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 = ON) ON [PRIMARY]
  9. ) ON [PRIMARY]
  11. GO
  13. ALTER TABLE [dbo].[CompanyProduct] WITH CHECK ADD CONSTRAINT [FK_CompanyProduct_Company] FOREIGN KEY([CompanyId])
  14. REFERENCES [dbo].[Company] ([CompanyId])
  15. GO
  17. ALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT [FK_CompanyProduct_Company]
  18. GO
  20. ALTER TABLE [dbo].[CompanyProduct] WITH CHECK ADD CONSTRAINT [FK_CompanyProduct_Product] FOREIGN KEY([ProductId])
  21. REFERENCES [dbo].[Product] ([ProductId])
  22. GO
  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. )
  14. ,1,1,'') as ProductList
  15. ) as X
Happy Coding