Converting values into Rows

Dividing any data value into different records by using any dividing criteria is always been interesting, today I am going to describe one of many ways to divide any values into rows by using any specified criteria , In this blog, I will explain how to divide a comma “,” separated value into rows and then as an extension we can add “-“ also as a dividend.

Let’s create a Table and insert some dummy records for Demo.

CREATE TABLE [dbo].[department](
      [dept_id] [numeric](18, 0) NULL,
      [sub_dept_id] [varchar](5000) NULL
) ON [PRIMARY]
GO

Inserting Dummy Records

INSERT INTO DBO.department VALUES(1,'1001,1002,1003-1004,1005,1006,1007-1009')
INSERT INTO DBO.department VALUES(2,'2001-2002,2003,2004,2005,2006,2007,2008, 2009-2010')

Select inserted records 

By Running Script for Comma Seperated values :

Here you can see script have divided values into rows by using comma seperated method , but still record with " - " are togather. If I want to seperate these records also then I will use previous scripts values and by using those values i'll again purify the result but this time criteria will be " - ".

In above mentioned code you can see we used same script by changing divident criteria from " , " to " - ", and FROM clause from actual table name to "Already fetched data" which is TEMP.

By using this mehtod you can add as many criteria's as your requirement.