CREATE TABLE #TmpTbl (TblID INT PRIMARY KEY, MultiDelimStr VARCHAR(255))INSERT #TmpTbl ( TblID, MultiDelimStr )SELECT 101, 'Orange,Apple;Orange,Grape,Banana;Apple,Kiwi,Grape' UNIONSELECT 202, 'Grape,Apple;Orange,Grape,Banana;Peach,Kiwi,Grape' UNIONSELECT 303, 'Orange;Peach,Grape,Banana;Apple,Kiwi;Tangerine,Grapefruit'
101 Orange,Grape,Apple101 Orange,Grape,Kwi101 Orange,Banana,Apple101 Orange,Banana,Kiwi101 Orange,Banana,Grape101 Apple,Orange,Kiwi101 Apple,Orange,Grape101 Apple,Grape,Kiwi101 Apple,Banana,Kiwi101 Apple,Banana,Grape202 Grape,Orange,Peach202 Grape,Orange,Kiwi202 Grape,Banana,Peach202 Grape,Banana,Kiwi202 Apple,Orange,Peach202 Apple,Orange,Kiwi202 Apple,Orange,Grape202 Apple,Grape,Peach202 Apple,Grape,Kiwi202 Apple,Banana,Grape202 Apple,Banana,Peach202 Apple,Banana,Kiwi303 Orange,Peach,Apple,Tangerine303 Orange,Peach,Apple,Grapefruit303 Orange,Peach,Kiwi,Tangerine303 Orange,Peach,Kiwi,Grapefruit303 Orange,Grape,Apple,Tangerine303 Orange,Grape,Apple,Grapefruit303 Orange,Grape,Kiwi,Tangerine303 Orange,Grape,Kiwi,Grapefruit303 Orange,Banana,Apple,Tangerine303 Orange,Banana,Apple,Grapefruit303 Orange,Banana,Kiwi,Tangerine303 Orange,Banana,Kiwi,Grapefruit
101 Apple,Orange,Apple
SELECT FruitID, MultiDelimStr,RANK() OVER(PARTITION BY FruitID ORDER BY N) AS GroupID,SUBSTRING(';'+MultiDelimStr+';',N+1,CHARINDEX(';',';'+MultiDelimStr+';',N+1)-N-1) AS GroupFruitINTO #FirstParseFROM #FruitTbl FCROSS JOIN dbo.Tally TWhere T.N < LEN(';'+MultiDelimStr+';') AND SUBSTRING(';'+MultiDelimStr+';',N,1) = ';'--SELECT * FROM #FirstParseSELECT FruitID, GroupID,SUBSTRING(','+GroupFruit+',',N+1,CHARINDEX(',',','+GroupFruit+',',N+1)-N-1) AS SoloFruitINTO #SecondParseFROM #FirstParse FCROSS JOIN dbo.Tally TWhere T.N < LEN(','+GroupFruit+',') AND SUBSTRING(','+GroupFruit+',',N,1) = ','SELECT * FROM #SecondParse
FruitID GroupID SoloFruit101 1 Orange101 1 Apple101 2 Orange101 2 Grape101 2 Banana101 3 Apple101 3 Kiwi101 3 Grape
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' UNIONSELECT 202, 'Grape,Apple;Orange,Grape,Banana;Peach,Kiwi,Grape' UNIONSELECT 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 ACROSS 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
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 RETURNEND
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' UNIONSELECT 202, 'Grape,Apple;Orange,Grape,Banana;Peach,Kiwi,Grape' UNIONSELECT 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.FruitFROM CrossThree AS AINNER 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.FruitINNER 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.FruitWHERE B.NumGroups = 3UNION ALL SELECT DISTINCT B.TblId, B.Fruit + ',' + C.Fruit + ',' + D.Fruit + ',' + E.FruitFROM CrossFour AS AINNER 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.FruitINNER 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.FruitINNER 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.FruitWHERE B.NumGroups = 4
;WITH Splits AS ( SELECT TblId, GRP_ID = B.Id, C.Value AS Fruit FROM #TmpTbl AS A CROSS APPLY dbo.fSplit(MultiDelimStr, ';') AS B CROSS APPLY dbo.fSplit(B.Value, ',') AS C)SELECT one.tblID, one.Fruit + ',' + two.Fruit + ISNULL(',' + three.Fruit, '') + ISNULL(',' + four.Fruit, '') + ISNULL(',' + five.Fruit, '')FROM Splits oneLEFT JOIN Splits two on (one.tblID = two.tblID AND two.GRP_ID = 2)LEFT JOIN Splits three on (one.tblID = three.tblID AND three.GRP_ID = 3)LEFT JOIN Splits four on (one.tblID = four.tblID AND four.GRP_ID = 4)LEFT JOIN Splits five on (one.tblID = five.tblID AND five.GRP_ID = 5)WHERE ISNULL(one.Fruit,'') != ISNULL(two.Fruit,'*') and ISNULL(one.Fruit,'') != ISNULL(three.Fruit,'*') and ISNULL(one.Fruit,'') != ISNULL(four.Fruit,'*') and ISNULL(one.Fruit,'') != ISNULL(five.Fruit,'*') and ISNULL(two.Fruit,'') != ISNULL(three.Fruit,'*') and ISNULL(two.Fruit,'') != ISNULL(four.Fruit,'*') and ISNULL(two.Fruit,'') != ISNULL(five.Fruit,'*') and ISNULL(three.Fruit,'') != ISNULL(four.Fruit,'*') and ISNULL(three.Fruit,'') != ISNULL(five.Fruit,'*') and ISNULL(four.Fruit,'') != ISNULL(five.Fruit,'*') AND one.GRP_ID = 1