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:

Company - Contains company details
Products - Contains product details
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

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

CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED
(
[CompanyId] ASC
)

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

)

ON [PRIMARY]

GO

Product

CREATE TABLE [dbo].[Product]
(
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductId] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_L
OCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO

Company Product
CREATE TABLE [dbo].[CompanyProduct](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CompanyId] [int] NOT NULL,
[ProductId] [int] NOT NULL,
CONSTRAINT [PK_CompanyProduct] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CompanyProduct] WITH CHECK ADD CONSTRAINT [FK_CompanyProduct_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([CompanyId])
GO

ALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT [FK_CompanyProduct_Company]
GO

ALTER TABLE [dbo].[CompanyProduct] WITH CHECK ADD CONSTRAINT [FK_CompanyProduct_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([ProductId])
GO

ALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT [FK_CompanyProduct_Product]
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:

SELECT C.CompanyName, X.ProductList FROM
dbo.[Company] C
CROSS APPLY
(
SELECT STUFF
(
(
SELECT ',' + P.ProductName FROM dbo.[Product] AS P
INNER JOIN dbo.[CompanyProduct] AS CP ON P.ProductId=CP.ProductId
WHERE CP.CompanyId=C.CompanyId
FOR XML PATH('')
)
,1,1,'') as ProductList
) as X

Happy Coding