Help needed!! Split columns to rows for join

Question

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Check out this article[/url] which contains a set-based split methodology. You can replace the numbers table with a CTE.

Shaun-884394 Ten Centuries Points: 1096 More actions · Answer 2

Thanks Jack, Jeff and Jacob 🙂

I implemented it this way.

[Code]

DECLARE @Delim CHAR(1)

SET @Delim = ','

;WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows

L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows

L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows

L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows

L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows

--L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows

Tally AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)--L5)

,Source AS

(

-- Use actual table instead of query below

SELECT 1 AS ID, 'Tom' AS Name ,'1,2,3,4' AS FK

UNION

SELECT 2, 'Dick','3,4'

UNION

SELECT 3, 'Harry','1,5'

)

SELECT

ID

,Name

,FK = LTRIM(SUBSTRING(@Delim + s.FK, t.N + 1,CHARINDEX(@Delim, s.FK + @Delim, t.N + 1) - t.N))

FROM

Tally t

RIGHT OUTER JOIN Source s ON SUBSTRING(@Delim+s.FK, t.N, 1) = @Delim AND t.N < LEN(@Delim+s.FK)

[/Code]