splitting a row into multiple rows.

Question

Post reply

splitting a row into multiple rows.

sql_novice_2007

Hall of Fame

Points: 3307
More actions
July 9, 2008 at 4:23 am

#72480

I have a table with two columns ID and Names.
ID Names
1 david,peter,sam
2 john,kevin,marsh
3 brian,broad
I wanted to convert the above data as following
ID Names
1 david
1 peter
1 sam
2 john
2 kevin
2 marsh
3 brian
3 broad
How can I do this?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

You can do it by using Tally table or master..spt_values table.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 2

If you want to know more about 'Tally' table, read the following URL.

http://www.sqlservercentral.com/articles/TSQL/62867/

karthik

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

sql_novice_2007 (7/9/2008)
I have a table with two columns ID and Names.
ID Names
1 david,peter,sam
2 john,kevin,marsh
3 brian,broad
I wanted to convert the above data as following
ID Names
1 david
1 peter
1 sam
2 john
2 kevin
2 marsh
3 brian
3 broad
How can I do this?
Thanks.

In the article that Karthik pointed to (relisted here for convenience), there's a section called "One Final "Split" Trick with the Tally Table"... that contains the code you need to do your exact task.

http://www.sqlservercentral.com/articles/TSQL/62867/

If that doesn't quite do it for you, post a table CREATE and some data in the format identified in the URL in my signature line and we'll demo on that. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 4

It worked great for my requirement.Thanks for your help.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

You should still take the time to read the article about Tally tables whose link I posted, but here's the solution to the problem you posted. Also, take a look at the link in my signature for how to post data on a forum to get better answers quicker...

--===========================================================================================================

-- Create a demonstration table and populate it.

-- THIS IS NOT PART OF THE SOLUTION.

-- This is the best way to submit data on a forum.

-- See the link in may signature line for why

--===========================================================================================================

--===== Conditionally drop the demo table so can rerun code to "play"

IF OBJECT_ID('TempDB..#DemoTable','U') IS NOT NULL

DROP TABLE #DemoTable

--===== Create the demo table

CREATE TABLE #DemoTable

(ID INT, Names VARCHAR(100))

--===== Populate the demo table with test data from the forum post

INSERT INTO #DemoTable

(ID,Names)

SELECT '1','david,peter,sam' UNION ALL

SELECT '2','john,kevin,marsh' UNION ALL

SELECT '3',Null UNION ALL

SELECT '4','brian,broad' UNION ALL

SELECT '5','jeff'

--===========================================================================================================

-- Demonstrate the solution

--===========================================================================================================

--==== Split or "Normalize" the whole table at once

SELECT ID,

SUBSTRING(','+dt.Names+',',N+1,CHARINDEX(',',','+dt.Names+',',N+1)-N-1) AS ClientID

FROM dbo.Tally t

CROSS JOIN #DemoTable dt

WHERE N < LEN(','+dt.Names+',')

AND SUBSTRING(','+dt.Names+',',N,1) = ','

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)