Open In App

How to Turning a Comma Separated String into Individual Rows in SQL Server?

Improve
Improve
Like Article
Like
Save
Share
Report

Given a column in the table having strings with comma-separated values. The task is to split all the comma-separated values and insert them as new rows.

So, let’s start by creating a database first.

Step 1: Create a Database.

Query : 

CREATE DATABASE GFG

Step 2: Use the GFG Database.

Query :

USE GFG

Step 3: Create a table

Create a table (CommaSeparated) to store the data.

Query :

CREATE TABLE CommaSeparated(Strings VARCHAR(20));

Output :

 

Step 4: Insert some data into the database.

Query : 

INSERT INTO CommaSeparated
VALUES ('A,C'),
       ('B'),
       ('A,G'),
       ('C'),
       ('B');

Output :

 

Step 5: SQL query to split all the comma-separated values and insert them as new rows.

We can split the string using STRING_SPLIT method. Get the new rows by applying the CROSS APPLY method to this.

Query :

the  
  VALUE 
FROM 
  CommaSeparated CROSS APPLY STRING_SPLIT(Strings, ',');

Output :

 


Last Updated : 11 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads