Returning all rows with Split function

Question

Returning all rows with Split function

Darryl Jenkins

SSC Rookie

Points: 33
More actions
October 11, 2008 at 5:26 pm

#383040

I think I've outsmarted myself. I decided to store child data in one column of parent table as follows
ID Column ChildDataColumn
1 Test1 1;12;
2 Test2 2;15;
I can return the following with a Split function -
ID ChildDataColumn
1 1
1 12
but how do I return the following as a join to the parent table? -
ID ChildDataColumn
1 1
1 12
2 2
2 15
So that I come up with following -
ID Column Childdatacolumn
1 test 1
1 test 12
2 test2 2
2 test2 15
Any help is appreciated.

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

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

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 1

If you want to return a table's worth of splits, then you have to stop worrying about the rows and worry only about the column. 😀

I don't know if your split function uses a Tally table or not, but let's assume you don't know what I'm talking about. Please see the following article for what a Tally table is and how it's used to replace loops.

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

Ok... on to your problem... you need to split an entire table's worth of delimited items. Her's the test data and the solution...

--===== Create and populate a test table according to the requirements.

-- This is NOT part of the solution

CREATE TABLE #YourTable (ID INT PRIMARY KEY CLUSTERED, [Column] VARCHAR(10), ChildDataColumn VARCHAR(100))

INSERT INTO #YourTable

(ID,[Column],ChildDataColumn)

SELECT '1','Test1','1;12;' UNION ALL

SELECT '2','Test2','2;15;'

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

SELECT yt.ID,

yt.[Column],

SUBSTRING(';'+yt.ChildDataColumn,t.N+1,CHARINDEX(';',yt.ChildDataColumn,t.N)-t.N) AS Value

FROM dbo.Tally t

CROSS JOIN #YourTable yt

WHERE t.N < LEN(';'+yt.ChildDataColumn)

AND SUBSTRING(';'+yt.ChildDataColumn,N,1) = ';'

Also, for future posts, you'll get better answers quicker if you follow the suggestions in the link in my signature below.

--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)

Darryl Jenkins SSC Rookie Points: 33 More actions · Answer 2

Jeff,

Thank you for the help. I really appreciate it. Point taken for future posts.

Darryl Jenkins

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

I appreciate your concern about future posts... but more immediately, are you all set with the current post?

--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)

Darryl Jenkins SSC Rookie Points: 33 More actions · Answer 4

Darryl Jenkins

SSC Rookie

Points: 33

October 13, 2008 at 1:29 pm

#884316

Worked like a charm. Thanks again.

Darryl Jenkins

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

You bet... thanks for the feedback.

--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)