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 row, normally we are thinking about use of SQL functions, or SQL cursor or loop. Or sometimes we are writing lengthy codes to get the record as individual row.




This article provides code block which fetches each data from comma separated value in to 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.

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

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  @t  
 )t  
 CROSS APPLY x.nodes('/XMLRoot/RowData')SD(S)  

How this SQL Query Works

The above mentioned SQL Query first convert each data from comma separated value into 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.Hope you liked this blog, please share this blog to help others.

Thanks
Kailash Chandra Behera