Fetching comma separated value as multiple rows in SQL

Introduction

In our daily development cycle, sometimes we are in situation that in a SQL single column of table we are storing multiple data with comma separated format. Late we want fetch those record as individual row.

To fetch those comma-separated records as individual rows, normally we are thinking about the use of SQL functions, or SQL cursor or loop. Or sometimes we are writing lengthy codes to get the record as an individual row.

This article provides a code block that fetches each data from comma-separated value into an individual row without using any SQL function or SQL cursor or loop.

Getting Started

Let’s say we have on table name student details having with columns, Student Name, Student Course. This table stores name of course in Student Course column, check the blow image to know structure of table.

SPLIT STRING SQL

Now as discussed in above, we will display the data in the following way as shown in the image.

SPLIT STRING SQL SERVER

This following below code fetches data from comma separated value in to new rows without using any user created function or lengthy code.

 SELECT StudentName,  
 LTRIM(RTRIM(SD.S.value('.[1]','varchar(8000)'))) AS StudentCourse  
 FROM  
 (  
 SELECT StudentName,CAST('<XMLRoot><RowData>' + REPLACE(StudentCourse,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x  
 FROM  StudentDetails  
 )t  
 CROSS APPLY x.nodes('/XMLRoot/RowData')SD(S)  

How this SQL Query Works

The above-mentioned SQL Query first converts each data from comma-separated value into the SQL node and creates an XML data by consolidating all the nodes.

Then again it fetches each node from XML data as table row and displays in the result windows. I hope you liked this blog, please share this blog to help others.

Thanks