Hi all,

I have a table A with 2 cols one id and the other linkedids separated with
pipe "|"

id    linked ids
1    4|5|6
2    9|10|11

I want the output as without using cursors or while loop..

1    4
1    5
1    6
2    9
2    10
2    11

Your thoughts on this...

Thanks in advance,
Pradeep

See this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html

Razvan

And my personal favourite:
http://solidqualitylearning.com/Blogs/dejan/archive/2004/10/22/200.aspx


ML

This cheeky little solution can deal with up to four items ( three pipes ):

SET NOCOUNT ON

DROP TABLE #linked_ids
CREATE TABLE #linked_ids ( id INT PRIMARY KEY, linked_ids VARCHAR( 50 ) )

INSERT INTO #linked_ids
SELECT 1, '4|5|6|7'      UNION
SELECT 2, '9|10|11|12'   UNION
SELECT 3, '13|14|15|16|17'

SET NOCOUNT OFF

SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 1 )
FROM #linked_ids t
UNION
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 2 )
FROM #linked_ids t
UNION
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 3 )
FROM #linked_ids t
UNION
SELECT t.id, t.linked_ids, PARSENAME( REPLACE( t.linked_ids, '|', '.' ), 4)
FROM #linked_ids t


Damien

Show quoteHide quote

See: http://tinyurl.com/b3ce2

Dangers of update statement
Automating database changes
[OT] SQL Humor
estimate size of table based on number of rows
Need Query Help