Make Row Values In Comma Separate

Introduction

In this blog, I will explain how to create and make columns with the comma a separate value, using a simple query. In this blog, you will learn a SQL query to make commas separate columns. There is no need to use any complex query. I have done this type of work in my project. Thus, you can customize, according to your requirement.

What is the scenario?

Actually, I have a single table, which has 3 columns (Reg_Id,Name,Address). We can make a new column to save comma separated values from the Address column after checking the condition, if the name is the same in the multiple rows and also check whether Reg_Id matches other rows. I am giving an example with SQL query.

Step 1: Create a table using SQL query, given below:

------Create a table tbl_T1----------------------------------------
CREATE TABLE [dbo].[tbl_Test](
    [Reg_Id] [varchar](10) NULL,
    [Name] [varchar](10) NULL,
    [Address] [varchar](50) NULL
) ON [PRIMARY]

Step 2: After creating a table, insert value in the table. Use the query, given below:

insert into tbl_Test(Reg_Id,Name,Address) values('reg1','Alok','Noida'),
('reg2','Alok','Noida'),
('reg3','Alok','Noida'),
('reg1','Alok','Delhi'),
('reg2','Alok','Noida'),
('reg1','Aman','Lucknow')

Step 3: Create a query to achieve your output, using a query, given below:

SELECT DISTINCT p.Reg_Id,p.Name,
  STUFF((SELECT distinct ',' + p1.Address
         FROM tbl_Test p1
         WHERE p.Name=p1.Name
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') CommaSeprated
FROM tbl_Test p

Step 4: Run the query to see the output.

Reg_Id  Name    CommaSeprated

reg1    Alok    Delhi,Noida
reg1    Aman    Lucknow
reg2    Alok    Delhi,Noida
reg3    Alok    Delhi,Noida

Description

In this query, I checked matching Names and made a comma separated value, which is stored in a CommaSeparated column. You can change the query according to your requirement.