Ok, I know this thread is old but if PatternSplitCM[/url] was available when this was posted you could do this:

DECLARE @string varchar(100)='1:1,3,5,7,4:56,43,58,5:34,67r,234';

WITH prep1 AS

(

SELECT x.ItemNumber,

ROW_NUMBER() OVER (PARTITION BY x.ItemNumber

ORDER BY xx.ItemNumber) AS rnk,

x.Item,

xx.Item AS Item_sub

FROM (SELECT * FROM dbo.PatternSplitCM(@string,'[^:]')

WHERE Item<>',') x

CROSS APPLY dbo.PatternSplitCM(x.Item,',') xx

WHERE x.Matched=1 AND xx.Matched=0

),

prep2 AS

(

SELECTItemNumber+

CASE

WHEN rnk=MAX(rnk) OVER (PARTITION BY ItemNumber) AND

prep1.ItemNumber<>MAX(ItemNumber) OVER()

THEN 2 ELSE 0

END AS ItemNumber,

Item_sub,

CASE

WHEN rnk=MAX(rnk) OVER (PARTITION BY ItemNumber) AND

prep1.ItemNumber<>MAX(ItemNumber) OVER()

THEN 1 ELSE 0

END AS isParent

FROM prep1

)

SELECT p1.Item_sub, p2.Item_sub xx

FROM prep2 p1

JOIN prep2 p2 ON p1.ItemNumber=p2.ItemNumber

WHERE p1.isParent=1

AND p2.isParent=0

ORDER BY p1.Item_sub