How to Turning a Comma Separated String into Individual Rows in SQL Server?
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 :