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