Sql Server query 2005

Question

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

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

First of all, for all your future posts read this article on how to post questions to getter better responses[/url]

Secondly, read about the article on how a tally table replaces a loop[/url]

And lastly, here is the sample code from the article on how to split a delimited column into rows.

IF ( OBJECT_ID( 'tempdb..#MyHead' ) IS NOT NULL )

DROP TABLE #MyHead

--===== Create a sample denormalized table with a CSV column

CREATE TABLE #MyHead

(PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

CsvColumn VARCHAR(500))

INSERT INTO #MyHead

SELECT '1,5,3,7,8,2' UNION ALL

SELECT '7,2,3,7,1,2,2' UNION ALL

SELECT '4,7,5' UNION ALL

SELECT '1' UNION ALL

SELECT '5' UNION ALL

SELECT '2,6' UNION ALL

SELECT '1,2,3,4,55,6'

--===== Split or "Normalize" the whole table at once using CROSS JOIN

SELECT mh.PK,

SUBSTRING(','+mh.CsvColumn+',',N+1,CHARINDEX(',',','+mh.CsvColumn+',',N+1)-N-1) AS Value

FROM RnD.dbo.Tally t

CROSS JOIN #MyHead mh

WHERE N < LEN(','+mh.CsvColumn+',')

AND SUBSTRING(','+mh.CsvColumn+',',N,1) = ','

--===== Split or "Normalize" the whole table at once using CROSS APPLY

SELECT mh.PK, t.Value

FROM #MyHead mh

CROSS APPLY

(

SELECTSUBSTRING(','+mh.CsvColumn+',',N+1,CHARINDEX(',',','+mh.CsvColumn+',',N+1)-N-1) AS Value

FROMRnD.dbo.Tally

WHERE N < LEN(','+mh.CsvColumn+',')

AND SUBSTRING(','+mh.CsvColumn+',',N,1) = ','

) t

--Ramesh

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Hi

There are different ways of doing this. You can use a function or use the number table method. Lots of info regarding this on the web and this site.

For the number table method check this

For a user defined function

"Keep Trying"