<< Sum up a tree hierachy in SQL Server 2005 | Home | Getting errors when working with Excel and SQL Server >>

Simulating cross apply with CSV-records in SQL Server 2000

DECLARE @Sample TABLE (Col1 VARCHAR(6), Col3 VARCHAR(200))

INSERT @Sample
SELECT '123', '125,124,126' UNION ALL
SELECT '124', '127,21,245'

--SELECT         Col1,
--                Data
--FROM            @Sample
--CROSS APPLY     fnParseList(',', Col3)

SELECT          a.Col1,
                SUBSTRING(',' + a.Col3 + ',', n.Number + 1, CHARINDEX(',', ',' + a.Col3 + ',', n.Number + 1) - n.Number - 1) AS [Value]
FROM            @Sample AS a
INNER JOIN      master..spt_values AS n ON SUBSTRING(',' + a.Col3 + ',', n.Number, 1) = ','
WHERE           n.Type = 'p'
                AND n.Number > 0 
                AND n.Number < LEN(',' + a.Col3 + ',')
kick it on DotNetKicks.com
Print | posted on Monday, October 15, 2007 8:25 AM

Feedback

# re: Cross apply in SQL Server 2000

left by Mladen at 10/15/2007 11:56 AM
couldn't resist huh :))
kick it!!

# re: Cross apply in SQL Server 2000

left by Jeff at 10/17/2007 10:25 PM
I don't get it. That's just a JOIN. You should title the post "parsing CSV's set-based" or something like that, but it has nothing to do with CROSS APPLY.

# re: Simulating cross apply with CSV-records in SQL Server 2000

left by Peter Larsson at 10/18/2007 7:49 AM
Noted. And title is changed.

# re: Simulating cross apply with CSV-records in SQL Server 2000

left by Madhivanan at 10/18/2007 3:01 PM
Do you also mean that your approach is alternate of this?


select
t1.col1,t2.number
from
@sample t1
cross join (
select number from master..spt_values
where Type = 'p'
AND Number > 0
) t2
where
','+cast(t1.col3 as varchar(2000))+',' like '%,'+cast(t2.number as varchar(10))+',%'

# re: Simulating cross apply with CSV-records in SQL Server 2000

left by Madhivanan at 10/18/2007 3:10 PM
Well. Ignore my comment. It wont work for all set of data

# re: Simulating cross apply with CSV-records in SQL Server 2000

left by sdfs at 1/27/2009 6:12 AM
useless
Title  
Name
Email (never displayed)
Url
Comments   
Please add 3 and 8 and type the answer here:
Copyright © Peter Larsson