|
|
Posted by rcamarda on 06/27/07 15:15
Thanks for the link to Erland's article's, it is very handy.
I tried your suggestion but I am confused by the WITH.
I tried
SELECT * FROM F_ADS_MANAGERS
WITH Managers
<snip your code>
and I got an error. Haven't seen this before so I am not sure how to
trouble shoot.
Error I received:
Msg 336, Level 15, State 1, Line 2
Incorrect syntax near 'Managers'. If this is intended to be a common
table expression, you need to explicitly terminate the previous
statement with a semi-colon.
On Jun 27, 10:01 am, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> 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 Ratchevhttp://www.SQLStudio.com
Navigation:
[Reply to this message]
|