[SQL Server]WITH的遞迴應用-Split欄位

在網路上常看到一個問題,就是在一個欄位中存了類似 001, 002, 003 這樣的值.
一般存了這樣的值, 是想要轉成如下的資料表去跟其他的資料表做 JOIN 或是透過 WHERE 去濾資料.

FID	MyField
1	001
1	002
1	003

網路常見的做法是將 MyField 的值轉成 "001,002,003," 讓每個項目都維持 "xxx," 的格式, 再用 LIKE 去查資料.
或是將欄位值取出後, 在程式中去 Split 這個資料, 然後做許多的資料處理.
現在在 SQL 2005 中, 可以透過 WITH 的方式遞迴地將資料形成如上的表格:
例如以下的 SQL 句:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

WITH B (FID, CurrentItem, SubItem) AS (   SELECT     FID,     SUBSTRING(MyField,1,CHARINDEX(',', MyField)-1) CurrentItem,     SUBSTRING(MyField,CHARINDEX(',', MyField)+1,LEN(MyField)) SubItem   FROM MyTable --WHERE FID=1 這邊可以針對例如FID=1的資料做查詢   UNION ALL   SELECT     A.FID,     CASE WHEN CHARINDEX(',', SubItem)>0 THEN SUBSTRING(SubItem,1,CHARINDEX(',', SubItem)-1) ELSE SubItem END CurrentItem,     CASE WHEN CHARINDEX(',', SubItem)>0 THEN SUBSTRING(SubItem,CHARINDEX(',', SubItem)+1,LEN(SubItem)) ELSE '' END SubItem   FROM MyTable A   INNER JOIN B ON A.FID=B.FID AND SubItem<>''   --多一個SubItem<>''的條件判斷, 不然會無窮遞迴下去 )   SELECT * FROM B ORDER BY FID

查詢出來的資料表大致會像以下的樣子:

FID	CurrentItem	SubItem
1	001	002,003
1	002	003
1	003
2	002	004,005
2	004	005
2	005

後續透過 WITH 做出來的 B 表格就可以再去跟其它的表格做 JOIN, 或是透過 WHERE 去查出哪些 FID 的資料符合需求.