Simulating cross apply with CSV-records in SQL Server 2000

Mon Oct 15, 2007 by Peter Larsson in algorithms, 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 + ',')

Legacy Comments


Mladen 2007-10-15	re: Cross apply in SQL Server 2000 couldn't resist huh :)) kick it!!

Jeff 2007-10-17	re: Cross apply in SQL Server 2000 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.

Peter Larsson 2007-10-18	re: Simulating cross apply with CSV-records in SQL Server 2000 Noted. And title is changed.

Madhivanan 2007-10-18	re: Simulating cross apply with CSV-records in SQL Server 2000 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))+',%'

Madhivanan 2007-10-18	re: Simulating cross apply with CSV-records in SQL Server 2000 Well. Ignore my comment. It wont work for all set of data

sdfs 2009-01-27	re: Simulating cross apply with CSV-records in SQL Server 2000 useless

xman 2009-11-25	re: Simulating cross apply with CSV-records in SQL Server 2000 Awesome..Brilliant