SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Converting values into Rows

By Sheraz Mirza,

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.

Total article views: 710 | Views in the last 30 days: 11
 
Related Articles
FORUM

insert scripts

insert scripts

FORUM

sql code to divide header value based on number of detail records

need sample sql code to divide header value based on number of detail records

FORUM

Generate Insert script

Generate Insert script

BLOG

SQL Server – TSql to find Records matching certain criteria in all the tables of a DB.

Generally, we try to find out records matching a certain criteria from a single or few tables. Howev...

BLOG

SQL Server – TSql to find Records matching certain criteria in all the tables of a DB.

Generally, we try to find out records matching a certain criteria from a single or few tables. Howev...

 
Contribute