Post
Ask Question

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:
  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.