I'm not sure I understand all your requirements.

Why ID = 101 has 3 combinations? There are 4 fruits there...

Anyway this is what I could come up with. It could be a starter.

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 Pass1 AS (

SELECT TblId, C.Value

FROM #TmpTbl AS A

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

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

),

Pass2 AS (

SELECT *

FROM (

SELECT DISTINCT *

FROM Pass1

) AS A

),

Ids AS (

SELECT DISTINCT TblId

FROM #TmpTbl

)

SELECT A.TblId, B.*

FROM Pass2 AS A

CROSS APPLY (

SELECT CStr = A.Value + (

SELECT ',' + Value AS [text()]

FROM Pass2 AS P1

WHERE P1.TblId = A.TblId

AND P1.Value <> A.Value

ORDER BY Value

FOR XML PATH('')

)

)AS B

This code uses a split function by Jeff Moden that can be found here: http://www.sqlservercentral.com/articles/T-SQL/62867/

I'm including the function here:

CREATE FUNCTION [dbo].[fSplit]

(

@Parameter VARCHAR(8000),

@SplitOn char(1)

)

RETURNS @Elements TABLE

(

ID INT identity(1,1),

Value VARCHAR(8000)

)

AS

BEGIN

--===== Add start and end commas to the Parameter so we can handle

-- single elements

SET @Parameter = @splitOn + @Parameter + @SplitOn

--===== Join the Tally table to the string at the character level and

-- when we find a comma, insert what's between that command and

-- the next comma into the Elements table

INSERT INTO @Elements (Value)

SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(@splitOn,@Parameter,N+1)-N-1)

FROM dbo.Tally

WHERE N < LEN(@Parameter)

AND SUBSTRING(@Parameter,N,1) = @splitOn --Notice how we find the comma

RETURN

END

I'm sure this is not what you're after...

Good luck anyway