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:
  1. ------Create a table tbl_T1----------------------------------------
  2. CREATE TABLE [dbo].[tbl_Test](
  3. [Reg_Id] [varchar](10) NULL,
  4. [Name] [varchar](10) NULL,
  5. [Address] [varchar](50) NULL
  6. ) ON [PRIMARY]
Step 2: After creating a table, insert value in the table. Use the query, given below:
  1. insert into tbl_Test(Reg_Id,Name,Address) values('reg1','Alok','Noida'),
  2. ('reg2','Alok','Noida'),
  3. ('reg3','Alok','Noida'),
  4. ('reg1','Alok','Delhi'),
  5. ('reg2','Alok','Noida'),
  6. ('reg1','Aman','Lucknow')
Step 3: Create a query to achieve your output, using a query, given below:
  1. SELECT DISTINCT p.Reg_Id,p.Name,
  2. STUFF((SELECT distinct ',' + p1.Address
  3. FROM tbl_Test p1
  4. WHERE p.Name=p1.Name
  5. FOR XML PATH(''), TYPE
  6. ).value('.', 'NVARCHAR(MAX)')
  7. ,1,1,'') CommaSeprated
  8. FROM tbl_Test p
Step 4: Run the query to see the output.
  1. Reg_Id Name CommaSeprated

  3. reg1 Alok Delhi,Noida
  4. reg1 Aman Lucknow
  5. reg2 Alok Delhi,Noida
  6. 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.