I must say I'm quite disappointed I could not solve this problem in a simple way.

Maybe It's something beyond my skills.

Anyway this is the best I could put together:

IF OBJECT_ID('Tempdb..#TmpTbl') IS NOT NULL DROP TABLE #TmpTbl

CREATE TABLE #TmpTbl (TblID INT PRIMARY KEY, MultiDelimStr VARCHAR(255))

INSERT #TmpTbl ( TblID, MultiDelimStr )

SELECT 101, 'Orange,Apple;Orange,Grape,Banana;Apple,Kiwi,Grape'

UNION

SELECT 202, 'Grape,Apple;Orange,Grape,Banana;Peach,Kiwi,Grape'

UNION

SELECT 303, 'Orange;Peach,Grape,Banana;Apple,Kiwi;Tangerine,Grapefruit'

;WITH Splits AS (

SELECT TblId, GRP_ID = B.Id, C.Value AS Fruit, C.Id AS Fruit_id, NumGroups = MAX(B.Id) OVER(PARTITION BY TblId)

FROM #TmpTbl AS A

CROSS APPLY dbo.fSplit(MultiDelimStr, ';') AS B

CROSS APPLY dbo.fSplit(B.Value, ',') AS C

),

ThreeRows AS (

SELECT N

FROM Tally

WHERE N <= 3

),

FourRows AS (

SELECT N

FROM Tally

WHERE N <= 4

),

CrossThree AS (

SELECT A.N AS [1], B.N AS [2], C.N AS [3]

FROM ThreeRows AS A

CROSS JOIN ThreeRows AS B

CROSS JOIN ThreeRows AS C

),

CrossFour AS (

SELECT A.N AS [1], B.N AS [2], C.N AS [3], D.N AS [4]

FROM FourRows AS A

CROSS JOIN ThreeRows AS B

CROSS JOIN ThreeRows AS C

CROSS JOIN ThreeRows AS D

)

SELECT DISTINCT B.TblId, B.Fruit + ',' + C.Fruit + ',' + D.Fruit

FROM CrossThree AS A

INNER JOIN Splits AS B

ON B.GRP_ID = 1

AND B.Fruit_ID = [1]

INNER JOIN Splits AS C

ON C.GRP_ID = 2

AND C.TblId = B.TblId

AND C.Fruit_ID = [2]

AND C.Fruit <> B.Fruit

INNER JOIN Splits AS D

ON D.GRP_ID = 3

AND D.TblId = C.TblId

AND D.Fruit_ID = [3]

AND D.Fruit <> B.Fruit

AND D.Fruit <> C.Fruit

WHERE B.NumGroups = 3

UNION ALL

SELECT DISTINCT B.TblId, B.Fruit + ',' + C.Fruit + ',' + D.Fruit + ',' + E.Fruit

FROM CrossFour AS A

INNER JOIN Splits AS B

ON B.GRP_ID = 1

AND B.Fruit_ID = [1]

INNER JOIN Splits AS C

ON C.GRP_ID = 2

AND C.TblId = B.TblId

AND C.Fruit_ID = [2]

AND C.Fruit <> B.Fruit

INNER JOIN Splits AS D

ON D.GRP_ID = 3

AND D.TblId = C.TblId

AND D.Fruit_ID = [3]

AND D.Fruit <> B.Fruit

AND D.Fruit <> C.Fruit

INNER JOIN Splits AS E

ON E.GRP_ID = 4

AND E.TblId = D.TblId

AND E.Fruit_ID = [4]

AND E.Fruit <> B.Fruit

AND E.Fruit <> C.Fruit

AND E.Fruit <> D.Fruit

WHERE B.NumGroups = 4

Basically the code builds two work tables (CrossThree and CrossFour) with "N" columns, holding all N^N possible combinations of values between 1 and "N".

Once the work tables are in place, I join them back to the splitted strings, choosing the right worktable looking at the number of groups.

The main thing I don't like about this code is that it relies on fixed numbers of groups, so that it should be generated dynamically after a first pass on the input data to gather the minimum/maximum number of groups.

I think this problem could be solved much better with a CLR aggregate, but it's a technique I don't master.

I hope someone can help with something better than this.

Gianluca