-- Create our Consignment History table CREATE TABLE #ConsignmentHistory ( ConsignmentNo VARCHAR(20), PkgNo INT, PRIMARY KEY (ConsignmentNo, PkgNo) ); -- Insert some small sequences of Items INSERT INTO #ConsignmentHistory SELECT '2322',1 UNION ALL SELECT '2322',3 UNION ALL SELECT '2322',4 UNION ALL SELECT '2343',1 UNION ALL SELECT '2343',2 UNION ALL SELECT '2343',5 UNION ALL SELECT '2343',6 UNION ALL SELECT '7611',1 UNION ALL SELECT '7611',2 UNION ALL SELECT '7611',4 UNION ALL SELECT '7611',6 UNION ALL SELECT '5195',7 UNION ALL SELECT '5195',1 UNION ALL SELECT '5195',2 UNION ALL SELECT '5195',5; -- Insert a longer sequence of packages with some gaps WITH Tally (n) AS ( SELECT number FROM [master].dbo.spt_values WHERE [Type] = 'P' AND Number BETWEEN 1 AND 50) INSERT INTO #ConsignmentHistory SELECT '4385', n FROM Tally WHERE 1 <> n%8; -- Insert the longest sequence of packages with no gaps WITH Tally (n) AS ( SELECT number FROM [master].dbo.spt_values WHERE [Type] = 'P' AND Number BETWEEN 1 AND 100) INSERT INTO #ConsignmentHistory SELECT '6377', n FROM Tally; SELECT * FROM #ConsignmentHistory ORDER BY ConsignmentNo, PkgNo; -- A second table to store our de-normalized views of packages CREATE TABLE #ConsHistoryDelimited ( ConsignmentNo VARCHAR(20) PRIMARY KEY, PkgNo VARCHAR(8000), StartPkgNo INT, EndPkgNo INT, CondensedPkgNos VARCHAR(8000), MissingPkgNos VARCHAR(8000) ); -- Construct the delimited list using the standard FOR XML PATH method INSERT INTO #ConsHistoryDelimited (ConsignmentNo, PkgNo, StartPkgNo, EndPkgNo) SELECT ConsignmentNo, STUFF(( SELECT ', ' + CAST(PkgNo AS VARCHAR(5)) FROM #ConsignmentHistory b WHERE a.ConsignmentNo = b.ConsignmentNo ORDER BY b.ConsignmentNo FOR XML PATH('')), 1, 2, '') -- Ignore these for now (we'll find a use for them later) ,MIN(PkgNo), MAX(PkgNo) FROM #ConsignmentHistory a GROUP BY ConsignmentNo; SELECT ConsignmentNo, PkgNo FROM #ConsHistoryDelimited WHERE ConsignmentNo IN ('2322','2343','7611','5195'); -- Display the "islands" (ranges) of contiguous packages for each consignment WITH Islands AS ( SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo) FROM ( SELECT ConsignmentNo, PkgNo -- This rn represents the "staggered rows" ,rn=PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY PkgNo) FROM #ConsignmentHistory) a GROUP BY ConsignmentNo, rn) SELECT ConsignmentNo, StartPkgNo, EndPkgNo FROM Islands ORDER BY ConsignmentNo, StartPkgNo; -- Using the islands, put the delimited list with hyphenated package number -- ranges into our table WITH Islands AS ( SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo) FROM ( SELECT ConsignmentNo, PkgNo -- This rn represents the "staggered rows" ,rn=PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY PkgNo) FROM #ConsignmentHistory) a GROUP BY ConsignmentNo, rn) UPDATE a SET CondensedPkgNos=STUFF(( SELECT ', ' + CASE -- Include either a single Item or the range (hyphenated) WHEN StartPkgNo = EndPkgNo THEN CAST(StartPkgNo AS VARCHAR(5)) ELSE CAST(StartPkgNo AS VARCHAR(5)) + '-' + CAST(EndPkgNo AS VARCHAR(5)) END FROM Islands b WHERE a.ConsignmentNo = b.ConsignmentNo ORDER BY StartPkgNo FOR XML PATH('')), 1, 2, '') FROM #ConsHistoryDelimited a; SELECT ConsignmentNo, PkgNo, CondensedPkgNos FROM #ConsHistoryDelimited; -- Starting with the Delimited list, construct the condensed delimited list WITH Islands AS ( SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo) FROM ( SELECT ConsignmentNo, c.PkgNo ,rn=c.PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY c.PkgNo) -- Changes to this CTE start here FROM #ConsHistoryDelimited a CROSS APPLY dbo.DelimitedSplit8K(a.PkgNo, ',') b CROSS APPLY (SELECT CAST(Item AS INT)) c(PkgNo) -- And end here ) a GROUP BY ConsignmentNo, rn) SELECT ConsignmentNo, a.PkgNo, CondensedPkgNos=STUFF(( SELECT ', ' + CASE -- Include either a single Item or the range (hyphenated) WHEN StartPkgNo = EndPkgNo THEN CAST(StartPkgNo AS VARCHAR(5)) ELSE CAST(StartPkgNo AS VARCHAR(5)) + '-' + CAST(EndPkgNo AS VARCHAR(5)) END FROM Islands b WHERE a.ConsignmentNo = b.ConsignmentNo ORDER BY StartPkgNo FOR XML PATH('')), 1, 2, '') FROM #ConsHistoryDelimited a; -- Our Islands CTE creates the ranges of package numbers WITH Islands AS ( SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo) FROM ( SELECT ConsignmentNo, PkgNo -- This rn represents the "staggered rows" ,rn=PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY PkgNo) FROM #ConsignmentHistory) a GROUP BY ConsignmentNo, rn) -- Convert the Islands to Gaps (missing) packages using CROSS APPLY VALUES ,Islands2Gaps AS ( SELECT ConsignmentNo, GapStartPkgNo=MIN(PkgNo), GapEndPkgNo=MAX(PkgNo) FROM ( SELECT ConsignmentNo, PkgNo ,rn=ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY (SELECT NULL))/2 FROM Islands a CROSS APPLY (VALUES (StartPkgNo-1),(EndPkgNo+1)) b(PkgNo)) a GROUP BY ConsignmentNo, rn HAVING COUNT(PkgNo) = 2) -- We use correlated subqueries with FOR XML PATH to show how Islands and Gaps -- can be created at the same time. SELECT ConsignmentNo ,CondensedItemNos=STUFF(( SELECT ', ' + CASE -- Include either a single package or the range (hyphenated) WHEN StartPkgNo = EndPkgNo THEN CAST(StartPkgNo AS VARCHAR(5)) ELSE CAST(StartPkgNo AS VARCHAR(5)) + '-' + CAST(EndPkgNo AS VARCHAR(5)) END FROM Islands b WHERE a.ConsignmentNo = b.ConsignmentNo ORDER BY StartPkgNo FOR XML PATH('')), 1, 2, '') ,MissingPkgNos=STUFF(( SELECT ', ' + CASE -- Include either a single package or the range (hyphenated) WHEN GapStartPkgNo = GapEndPkgNo THEN CAST(GapStartPkgNo AS VARCHAR(5)) ELSE CAST(GapStartPkgNo AS VARCHAR(5)) + '-' + CAST(GapEndPkgNo AS VARCHAR(5)) END FROM Islands2Gaps b WHERE a.ConsignmentNo = b.ConsignmentNo ORDER BY GapStartPkgNo FOR XML PATH('')), 1, 2, '') FROM #ConsHistoryDelimited a; -- Our Islands CTE creates the ranges of package numbers WITH Islands AS ( SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo) FROM ( SELECT ConsignmentNo, PkgNo -- This rn represents the "staggered rows" ,rn=PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY PkgNo) FROM #ConsignmentHistory) a GROUP BY ConsignmentNo, rn) -- Convert the Islands to Gaps (missing) packages using CROSS APPLY VALUES ,Islands2Gaps AS ( SELECT ConsignmentNo, GapStartPkgNo=MIN(PkgNo), GapEndPkgNo=MAX(PkgNo) FROM ( SELECT ConsignmentNo, PkgNo ,rn=ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY (SELECT NULL))/2 FROM Islands a CROSS APPLY (VALUES (StartPkgNo-1),(EndPkgNo+1)) b(PkgNo)) a GROUP BY ConsignmentNo, rn HAVING COUNT(PkgNo) = 2) -- Update the missing package numbers column of our #ConsHistoryDelimited table UPDATE a SET MissingPkgNos=STUFF(( SELECT ', ' + CASE -- Include either a single package or the range (hyphenated) WHEN GapStartPkgNo = GapEndPkgNo THEN CAST(GapStartPkgNo AS VARCHAR(5)) ELSE CAST(GapStartPkgNo AS VARCHAR(5)) + '-' + CAST(GapEndPkgNo AS VARCHAR(5)) END FROM Islands2Gaps b WHERE a.ConsignmentNo = b.ConsignmentNo ORDER BY GapStartPkgNo FOR XML PATH('')), 1, 2, '') FROM #ConsHistoryDelimited a; SELECT ConsignmentNo, MissingPkgNos FROM #ConsHistoryDelimited; -- Exploding a condensed, delimited list WITH Tally (n) AS ( -- Restrict the rows in our 0-based Tally table to only what we need using TOP SELECT 0 UNION ALL SELECT TOP (( SELECT MAX(EndPkgNo)-MIN(StartPkgNo) FROM #ConsHistoryDelimited) ) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b) SELECT ConsignmentNo, PkgNo=n FROM #ConsHistoryDelimited a -- First CA to split the condensed list on the comma delimiter CROSS APPLY dbo.DelimitedSplit8K(CondensedPkgNos, ',') b -- Now retrieve the range or make the single entry into a range CROSS APPLY ( SELECT StartPkgNo=LEFT(Item, CHARINDEX('-', Item + '-') - 1) ,EndPkgNo=CASE CHARINDEX('-', Item) WHEN 0 THEN Item ELSE RIGHT(Item, LEN(Item) - CHARINDEX('-', Item)) END ) c -- Apply our Tally table to the range (the resulting n is our PkgNo) CROSS APPLY ( SELECT n=n+c.StartPkgNo FROM Tally WHERE n+c.StartPkgNo BETWEEN c.StartPkgNo AND c.EndPkgNo) d ORDER BY ConsignmentNo, n; GO DROP TABLE #ConsignmentHistory; DROP TABLE #ConsHistoryDelimited;