Posted by Plamen Ratchev on 06/27/07 14:01

Here is one method to split the list using a recursive CTE (this assumes SQL
Server 2005). It is not the fastest, but with a small size list to split it
should be OK.

WITH Managers
(manager_key, direct_reports_cn, start_pos, end_pos)
AS
(
SELECT manager_key,
direct_reports_cn + '|',
1,
CHARINDEX('|', direct_reports_cn + '|')
FROM F_ADS_MANAGERS
UNION ALL
SELECT manager_key,
direct_reports_cn,
end_pos + 1,
CHARINDEX('|', direct_reports_cn, end_pos + 1)
FROM Managers
WHERE CHARINDEX('|', direct_reports_cn, end_pos + 1) > 0
)
SELECT manager_key,
SUBSTRING(direct_reports_cn,
start_pos,
end_pos - start_pos) AS direct_report
FROM Managers
WHERE end_pos > 0;

Erland Sommarskog has a very good article for arrays and lists that covers
various techniques and analysis:
http://www.sommarskog.se/arrays-in-sql.html

HTH,

Plamen Ratchev
http://www.SQLStudio.com

• Next in forum: Re: mass alter table fields - script help
• Prev in forum: Re: Strange performance issue with UPDATE FROM
• Thread view: Re: Parse field into multiple rows

[Reply to this message]