May 18, 2009 at 7:25 am
Hi guys,
Lets see the source first.
WITH Test
AS
(
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 FROM Test
Output :
IDNameFK
1Tom1,2,3,4
2Dick3,4
3Harry1,5
I have to build a view which will give the output as follows
Desired Output:
IDNameFK
1Tom1
1Tom2
1Tom3
1Tom4
2Dick3
2Dick4
3Harry1
3Harry5
This would help me in easily creating joins on above view on the column FK.
Please help me out here. Hope i get some solution implemented with the use of CTE, as i believe it would be fastest.
May 18, 2009 at 8:14 am
Check out this article[/url] which contains a set-based split methodology. You can replace the numbers table with a CTE.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 18, 2009 at 8:51 am
Thanks Jack, Jeff and Jacob 🙂
I implemented it this way.
http://www.sqlservercentral.com/Forums/Topic465522-338-1.aspx?Highlight=split
http://www.sqlservercentral.com/articles/Datetime+Manipulation/61822/
[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]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply