/*测试数据*/
WITH tab
AS ( SELECT 1 id ,
'121500125' fru ,
'121500132' sub_fru
UNION ALL
SELECT 2 id ,
'121500125' fru ,
'5B19A4567R' sub_fru
UNION ALL
SELECT 3 id ,
'121500132' fru ,
'121500125' sub_fru
UNION ALL
SELECT 4 id ,
'121500132' fru ,
'5B19A4567R' sub_fru
UNION ALL
SELECT 5 id ,
'121500178' fru ,
'121500180' sub_fru
UNION ALL
SELECT 6 id ,
'121500180' fru ,
'121500178' sub_fru
UNION ALL
SELECT 7 id ,
'121500180' fru ,
'SB19A46291' sub_fru
),/*递归,遍历数据加入关系集合*/
t ( id, fru, sub_fru, fru_r, gp )
AS ( SELECT id ,
fru ,
sub_fru ,
CONVERT(VARCHAR(100), fru + ',' + sub_fru) AS fru_r ,--关系集合
1 AS gp--组号
FROM tab
WHERE id = 1
UNION ALL
SELECT t1.id ,
t1.fru ,
t1.sub_fru ,
CONVERT(VARCHAR(100), CASE WHEN ( CHARINDEX(t1.fru,
t2.fru_r) > 0
OR CHARINDEX(t1.sub_fru,
t2.fru_r) > 0
)--如果新产品和关系中的产品有关系，则加入
THEN CASE WHEN CHARINDEX(t1.fru,
t2.fru_r) = 0
THEN t2.fru_r
+ ',' + t1.fru
WHEN CHARINDEX(t1.sub_fru,
t2.fru_r) = 0
THEN t2.fru_r
+ ','
+ t1.sub_fru
ELSE t2.fru_r
END
ELSE t1.fru + ','
+ t1.sub_fru--新建另一组的关系集合
END) AS fru_r ,
CASE WHEN ( CHARINDEX(t1.fru, t2.fru_r) > 0
OR CHARINDEX(t1.sub_fru, t2.fru_r) > 0
) THEN t2.gp
ELSE t2.gp + 1
END AS gp
FROM tab t1 ,
t t2
WHERE t1.id = t2.id + 1
),/*取出每组集合中的最大子集*/
t1
AS ( SELECT gp AS id ,
MAX(fru_r) AS fru
FROM t
GROUP BY gp
)/*按逗号分割关系产品*/
SELECT t1.id ,
col = CAST(SUBSTRING(fru, number,
CHARINDEX(',', fru + ',', number) - number) AS VARCHAR(100))
FROM t1
CROSS APPLY master..spt_values
WHERE type = 'P'
AND number >= 1
AND number <= LEN(fru + 'a')
AND CHARINDEX(',', ',' + fru, number) = number
当然，也可以在递归的时候就把每组的产品都罗列出来，就省去后面拆分字符串的步骤。不过，
