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

In this blog we will explore how to get comma delimited values in SQL Server

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 

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