{"id":1693,"date":"2013-08-28T00:00:00","date_gmt":"2013-08-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/condensing-a-delimited-list-of-integers-in-sql-server\/"},"modified":"2021-08-24T13:39:51","modified_gmt":"2021-08-24T13:39:51","slug":"condensing-a-delimited-list-of-integers-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/condensing-a-delimited-list-of-integers-in-sql-server\/","title":{"rendered":"Condensing a Delimited List of Integers in SQL Server"},"content":{"rendered":"
\n

\tIn my work I support a couple of companies that are engaged in distribution of one kind or another. The distribution business is generally about moving boxes from point A to point B. A group of boxes going from one shipper location to a final consignee is called a shipment or a consignment. It is important to ensure that every box within the consignment is delivered, and to spot cases where boxes (components of a shipment) haven’t yet been passed through a particular checkpoint. During the lifetime of a consignment within the distribution network, each consignment goes through a sequence of statuses. For example, there’s one called “MDE” which is the time at which information from the consignment note (customer, addresses, etc.) is encoded into the application. Many of the consignment status codes are recorded by scanning a bar code, usually on a package, so there are two types of status: package and consignment level. Included in the package’s bar code is a unique package number within the consignment, e.g., 4 of 9. <\/p>\n

\tWhen a consignment enters or leaves a distribution center, it is a general practice to scan each package to a different status code. The application contains various forms for tracking the status of a shipment. When the form displays a consignment with many packages, this can result in a huge number of scans (rows) in the history, so in practice what is done is to simply construct a delimited list of package numbers that were each scanned to a status. <\/p>\n

\tWhen you’ve got 999 packages in a consignment, this will still result in a pretty long list of package numbers. <\/p>\n

\tBoth in commerce and science, we already have a way of representing ordered lists of ‘broken sequences.’ This allows us to describe islands of unbroken sequences economically as, for example, ‘2-8, 10-16, 18-24, 26-32, 34-40, 42-48. Even better, we can intermix these with the usual delimited list. ‘1, 2, 5-9’. The unbroken sequence from 1 to 100 would be very economically rendered as ‘1-100’, and it is easy to understand. <\/p>\n

\tSometimes it makes sense to use this representation of a sequence within an application. How can we handle this in SQL, turning it into a form that is easily manipulated by that relational system, and then back again into a text-based, delimited list? I’ll be showing you how in this article. I’ll first suggest how to condense a delimited list of integer package numbers into a list of unbroken ‘islands,’ show how to determine when these islands are missing packages and then go in the other direction by ‘exploding’ a condensed list. <\/p>\n

\tIf you’re a SQL enthusiast, you may want to look ahead to “The Final Word” section to see what algorithms will be combined in this article’s examples. <\/p>\n

Data Setup<\/h2>\n

\tIn order to explain and demonstrate the concept of condensing a delimited list, we’ll need to start by creating some sample data. For this, we’ll create two tables: <\/p>\n

    \n
  1. The first will contain a row set where the primary key the consignment number (a “class” of items) and also the package (or item) number within the class.<\/li>\n
  2. The second table will contain the same consignment number key but in its second column will be a delimited list of the packages that appeared as separate rows in our first table.<\/li>\n<\/ol>\n

    \tLet’s create and populate the first table (#ConsignmentHistory). For the purposes of our examples, we’re ignoring the status code that we mentioned above, or really what we’re doing is assuming that all of these examples are scans to the same status code. <\/p>\n

    -- Create our Consignment History table\nCREATE TABLE #ConsignmentHistory\n(\n\tConsignmentNo      VARCHAR(20),\n\tPkgNo              INT,\n    PRIMARY KEY (ConsignmentNo, PkgNo)\n);\n\n-- Insert some small sequences of packages\nINSERT INTO #ConsignmentHistory\nSELECT '2322',1 UNION ALL SELECT '2322',3 UNION ALL SELECT '2322',4\nUNION ALL SELECT '2343',1 UNION ALL SELECT '2343',2 UNION ALL SELECT '2343',5 \nUNION ALL SELECT '2343',6\nUNION ALL SELECT '7611',1 UNION ALL SELECT '7611',2 UNION ALL SELECT '7611',4 \nUNION ALL SELECT '7611',6  \nUNION ALL SELECT '5195',7 UNION ALL SELECT '5195',1 UNION ALL SELECT '5195',2 \nUNION ALL SELECT '5195',5;\n\n-- Insert a longer sequence of packages with some gaps\nWITH Tally (n) AS (\n    SELECT number\n    FROM [master].dbo.spt_values\n    WHERE [Type] = 'P' AND Number BETWEEN 1 AND 50)\nINSERT INTO #ConsignmentHistory\nSELECT '4385', n\nFROM Tally\nWHERE 1 <> n%8;\n\n-- Insert the longest sequence of packages with no gaps\nWITH Tally (n) AS (\n    SELECT number\n    FROM [master].dbo.spt_values\n    WHERE [Type] = 'P' AND Number BETWEEN 1 AND 100)\nINSERT INTO #ConsignmentHistory\nSELECT '6377', n\nFROM Tally;\n\nSELECT *\nFROM #ConsignmentHistory\nORDER BY ConsignmentNo, PkgNo;<\/pre>\n
     \tNote that for each of the consignment numbers except the last, there exist some gaps in the package numbers. We can use the familiar FOR XML PATH<\/a> method within a correlated sub-query to populate our second table, where the second column contains an ordered delimited list of the PkgNos. <\/p>\n
    -- A second table to store our de-normalized views of packages\nCREATE TABLE #ConsHistoryDelimited\n(\n\tConsignmentNo       VARCHAR(20) PRIMARY KEY,\n\tPkgNo               VARCHAR(8000),\n    StartPkgNo          INT,\n    EndPkgNo            INT,\n    CondensedPkgNos     VARCHAR(8000),\n    MissingPkgNos       VARCHAR(8000)\n);\n\n-- Construct the delimited list using the standard FOR XML PATH method\nINSERT INTO #ConsHistoryDelimited (ConsignmentNo, PkgNo, StartPkgNo, EndPkgNo)\nSELECT ConsignmentNo, STUFF((\n    SELECT ', ' + CAST(PkgNo AS VARCHAR(5))\n    FROM #ConsignmentHistory b\n    WHERE a.ConsignmentNo = b.ConsignmentNo\n    ORDER BY b.ConsignmentNo\n    FOR XML PATH('')), 1, 2, '')\n    -- Ignore these for now (we'll find a use for them later)\n    ,MIN(PkgNo), MAX(PkgNo)\nFROM #ConsignmentHistory a\nGROUP BY ConsignmentNo;\n\nSELECT ConsignmentNo, PkgNo\nFROM #ConsHistoryDelimited\nWHERE ConsignmentNo IN ('2322','2343','7611','5195');<\/pre>\n
     \tFor the time being, we will ignore the last 4 columns in the #ConsHistoryDelimited table, but we will be using them later. We can see that for the SELECTed results, our delimited lists are quite short: <\/p>\n
    ConsignmentNo  PkgNo\n2322           1, 3, 4\n2343           1, 2, 5, 6\n5195           1, 2, 5, 7\n7611           1, 2, 4, 6\n<\/pre>\n
     \tHowever if we were to examine the results for the last 2 consignments, you’d see that those lists are quite long. <\/p>\n
    Creating a Condensed List of Delimited Integer Items<\/h2>\n
     \tOne objective of this article is to take a delimited list of integers such as the ones we’ve stored in #ConsHistoryDelimited and condense them, in order to reduce the length of the string. Why would we want to do such a thing you might ask? A fair enough question, but first let’s start with our desired results set. <\/p>\n
    ConsignmentNo  PkgNo\n2322           1, 3-4\n2343           1-2, 5-6\n5195           1-2, 4, 6-7\n7611           1-2, 5\n4385           2-8, 10-16, 18-24, 26-32, 34-40, 42-48, 50\n6377           1-100\n<\/pre>\n
     \tBecause our first few lists are quite short to begin with, we haven’t achieved much of a reduction in length. We do see a difference for consignment number 7611 and a significant difference for consignment numbers 4385 and especially 6377. Such a shortening of the list of items could be quite useful if you need to display that list in your application’s front end. The condensed version may actually be easier for a viewer to grasp, especially if they want to know whether the list is complete or not. We have specifically included a space after the commas, so that when you display the delimited list in a form, the long list of items can be wrapped to multiple lines easily. <\/p>\n
     \tIn order to condense our list, we must draw upon the concept of Gaps and Islands<\/a>, but more specifically we need to group ranges (islands) of contiguous package numbers, similar to the way SQL MVP<\/a>Jeff Moden<\/a> demonstrated you can Group Islands of Contiguous Dates<\/a>. This technique was also demonstrated (possibly pre-dating Jeff’s article) by SQL MVP Itzik Ben-Gan<\/a> in Chapter 5 of the book SQL Server MVP Deep Dives<\/a>. Let’s return to our first table and calculate the islands utilizing this approach, which I’ve also heard referred to as the “staggered rows” approach to calculating islands: <\/p>\n
    -- Display the \"islands\" (ranges) of contiguous packages for each consignment\nWITH Islands AS (\n    SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo)\n    FROM (\n        SELECT ConsignmentNo, PkgNo\n            -- This rn represents the \"staggered rows\"\n            ,rn=PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY PkgNo)\n        FROM #ConsignmentHistory) a\n    GROUP BY ConsignmentNo, rn)\nSELECT ConsignmentNo, StartPkgNo, EndPkgNo\nFROM Islands\nORDER BY ConsignmentNo, StartPkgNo;\n\n<\/pre>\n
     \tWe chose this approach because it is pretty efficient and after a bit of study, reasonably easy to grasp conceptually. Here are the results: <\/p>\n
    ConsignmentNo   StartPkgNo   EndPkgNo\n2322            1            1\n2322            3            4\n2343            1            2\n2343            5            6\n4385            2            8\n4385            10           16\n4385            18           24\n4385            26           32\n4385            34           40\n4385            42           48\n4385            50           50\n5195            1            2\n5195            5            5\n5195            7            7\n6377            1            100\n7611            1            2\n7611            4            4\n7611            6            6\n<\/pre>\n
     \tTo construct our condensed list of package numbers, we need to combine the above Islands with the FOR XML PATH technique to generate the delimited list that is our desired results set. The only thing different than when we used FOR XML PATH when setting up the #ConsHistoryDelimited, is to CASE the islands that consist of a single PkgNo as a single integer rather than ending up with something like 3-3, as follows: <\/p>\n
    -- Using the islands, put the delimited list with hyphenated package number \n-- ranges into our table\nWITH Islands AS (\n    SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo)\n    FROM (\n        SELECT ConsignmentNo, PkgNo\n            -- This rn represents the \"staggered rows\"\n            ,rn=PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY PkgNo)\n        FROM #ConsignmentHistory) a\n    GROUP BY ConsignmentNo, rn)\nUPDATE a\nSET CondensedPkgNos=STUFF(( \n    SELECT ', ' + \n        CASE -- Include either a single Item or the range (hyphenated)\n            WHEN StartPkgNo = EndPkgNo THEN CAST(StartPkgNo AS VARCHAR(5))\n            ELSE CAST(StartPkgNo AS VARCHAR(5)) + '-' + CAST(EndPkgNo AS VARCHAR(5))\n            END\n    FROM Islands b\n    WHERE a.ConsignmentNo = b.ConsignmentNo\n    ORDER BY StartPkgNo\n    FOR XML PATH('')), 1, 2, '')\nFROM #ConsHistoryDelimited a;<\/pre>\n
     \tWe have chosen to UPDATE the corresponding column in our #ConsHistoryDelimited table with the condensed list of package numbers. Examination of the SELECTed results shows they are identical to the correct results we wanted to see! <\/p>\n
    Condensing a List of Delimited Items<\/h2>\n
     \tPerhaps we already have the delimited list of items to start with and we simply want to condense it. Now that we have an inkling of how to do it, we can easily use Jeff Moden’s “community,” delimited-string splitter, the well-known and very popular, best-of-breed DelimitedSplit8K<\/a> FUNCTION. In order to run the next example, you’ll need to download and install it from the linked article. <\/p>\n
    -- Starting with the Delimited list, construct the condensed delimited list\nWITH Islands AS (\n    SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo)\n    FROM (\n        SELECT ConsignmentNo, c.PkgNo\n            ,rn=c.PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY c.PkgNo)\n        -- Changes to this CTE start here\n        FROM #ConsHistoryDelimited a\n        CROSS APPLY dbo.DelimitedSplit8K(a.PkgNo, ',') b\n        CROSS APPLY (SELECT CAST(Item AS INT)) c(PkgNo)\n        -- And end here\n        ) a\n    GROUP BY ConsignmentNo, rn)\nSELECT ConsignmentNo, a.PkgNo, CondensedPkgNos=STUFF(( \n    SELECT ', ' + \n        CASE -- Include either a single Item or the range (hyphenated) \n            WHEN StartPkgNo = EndPkgNo THEN CAST(StartPkgNo AS VARCHAR(5))\n            ELSE CAST(StartPkgNo AS VARCHAR(5)) + '-' + CAST(EndPkgNo AS VARCHAR(5))\n            END\n    FROM Islands b\n    WHERE a.ConsignmentNo = b.ConsignmentNo\n    ORDER BY StartPkgNo\n    FOR XML PATH('')), 1, 2, '')\nFROM #ConsHistoryDelimited a;<\/pre>\n
     \tNote that only a very minor modification (look for the comment “Changes to this CTE start here”) was required to the Islands Common Table Expression (CTE) to use the delimited list stored in #ConsHistoryDelimited and split that string using DelimitedSplit8K. The Cascaded CROSS APPLY<\/a> is only there to avoid a few extra CASTs of Item (column output by DelimitedSplit8K) to INT. <\/p>\n
    Another Case to Consider<\/h2>\n
     \tSo what’s missing? More specifically, perhaps we’d like to see a condensed list of those package numbers that are missing for each consignment. That is the other half of the Gaps and Islands class of problems. We’d like to know what the gaps are. Any of the many methods for calculating gaps will do, but we’ll choose a somewhat obscure method of calculating gaps from islands using CROSS APPLY VALUES<\/a>. Using the Islands CTE that we already have, we can construct a query that will deliver a condensed delimited list of both Gaps and Islands: <\/p>\n
    -- Our Islands CTE creates the ranges of package numbers\nWITH Islands AS (\n    SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo)\n    FROM (\n        SELECT ConsignmentNo, PkgNo\n            -- This rn represents the \"staggered rows\"\n            ,rn=PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY PkgNo)\n        FROM #ConsignmentHistory) a\n    GROUP BY ConsignmentNo, rn)\n    -- Convert the Islands to Gaps (missing) packages using CROSS APPLY VALUES\n    ,Islands2Gaps AS (\n        SELECT ConsignmentNo, GapStartPkgNo=MIN(PkgNo), GapEndPkgNo=MAX(PkgNo)\n        FROM (\n            SELECT ConsignmentNo, PkgNo\n                ,rn=ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY (SELECT NULL))\/2\n            FROM Islands a\n            CROSS APPLY (VALUES (StartPkgNo-1),(EndPkgNo+1)) b(PkgNo)) a\n        GROUP BY ConsignmentNo, rn\n        HAVING COUNT(PkgNo) = 2)\n-- We use correlated subqueries with FOR XML PATH to show how Islands and Gaps\n-- can be created at the same time.\nSELECT ConsignmentNo\n    ,CondensedItemNos=STUFF(( \n        SELECT ', ' + \n            CASE -- Include either a single package or the range (hyphenated) \n                WHEN StartPkgNo = EndPkgNo THEN CAST(StartPkgNo AS VARCHAR(5))\n                ELSE CAST(StartPkgNo AS VARCHAR(5)) + '-' + CAST(EndPkgNo AS VARCHAR(5))\n                END\n        FROM Islands b\n        WHERE a.ConsignmentNo = b.ConsignmentNo\n        ORDER BY StartPkgNo\n        FOR XML PATH('')), 1, 2, '')\n    ,MissingPkgNos=STUFF(( \n        SELECT ', ' + \n            CASE -- Include either a single package or the range (hyphenated) \n                WHEN GapStartPkgNo = GapEndPkgNo THEN CAST(GapStartPkgNo AS VARCHAR(5))\n                ELSE CAST(GapStartPkgNo AS VARCHAR(5)) + '-' + CAST(GapEndPkgNo AS VARCHAR(5))\n                END\n        FROM Islands2Gaps b\n        WHERE a.ConsignmentNo = b.ConsignmentNo\n        ORDER BY GapStartPkgNo\n        FOR XML PATH('')), 1, 2, '')\nFROM #ConsHistoryDelimited a;<\/pre>\n
     \tNote that we have reverted to the Islands CTE that draws its data directly from the #ConsignmentHistory table. The results output from this query are: <\/p>\n
    ConsignmentNo  CondensedItemNos                             MissingPkgNos\n2322           1, 3-4                                       2\n2343           1-2, 5-6\t                                    3-4\n4385           2-8, 10-16, 18-24, 26-32, 34-40, 42-48, 50   9, 17, 25, 33, 41, 49\n5195           1-2, 5, 7                                    3-4, 6\n6377           1-100                                        NULL\n7611           1-2, 4, 6                                    3, 5\n<\/pre>\n
     \tIf we prefer, we can UPDATE the missing packages into our table. <\/p>\n
    -- Our Islands CTE creates the ranges of package numbers\nWITH Islands AS (\n    SELECT ConsignmentNo, StartPkgNo=MIN(PkgNo), EndPkgNo=MAX(PkgNo)\n    FROM (\n        SELECT ConsignmentNo, PkgNo\n            -- This rn represents the \"staggered rows\"\n            ,rn=PkgNo-ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY PkgNo)\n        FROM #ConsignmentHistory) a\n    GROUP BY ConsignmentNo, rn)\n    -- Convert the Islands to Gaps (missing) packages using CROSS APPLY VALUES\n    ,Islands2Gaps AS (\n        SELECT ConsignmentNo, GapStartPkgNo=MIN(PkgNo), GapEndPkgNo=MAX(PkgNo)\n        FROM (\n            SELECT ConsignmentNo, PkgNo\n                ,rn=ROW_NUMBER() OVER (PARTITION BY ConsignmentNo ORDER BY (SELECT NULL))\/2\n            FROM Islands a\n            CROSS APPLY (VALUES (StartPkgNo-1),(EndPkgNo+1)) b(PkgNo)) a\n        GROUP BY ConsignmentNo, rn\n        HAVING COUNT(PkgNo) = 2)\n-- Update the missing package numbers column of our #ConsHistoryDelimited table\nUPDATE a\nSET MissingPkgNos=STUFF(( \n    SELECT ', ' + \n        CASE -- Include either a single package or the range (hyphenated) \n            WHEN GapStartPkgNo = GapEndPkgNo THEN CAST(GapStartPkgNo AS VARCHAR(5))\n            ELSE CAST(GapStartPkgNo AS VARCHAR(5)) + '-' + CAST(GapEndPkgNo AS VARCHAR(5))\n            END\n    FROM Islands2Gaps b\n    WHERE a.ConsignmentNo = b.ConsignmentNo\n    ORDER BY GapStartPkgNo\n    FOR XML PATH('')), 1, 2, '')\nFROM #ConsHistoryDelimited a;\n\nSELECT ConsignmentNo, MissingPkgNos\nFROM #ConsHistoryDelimited;<\/pre>\n
    Exploding the Condensed Delimited List<\/h2>\n
     \tTo explode a condensed, delimited list is to simply expand out any of the entries that include a hyphen. To do this, we’ll once again use DelimitedSplit8K (to split on comma separators) and the familiar concept of a Tally table. Pretty much any Tally table will do, whether it is a true table in your database or an in-line Tally table like I’ve done. To keep performance under control when using an in-line Tally table, make sure you don’t generate any more tally rows than you need, so check what I’ve done with TOP. <\/p>\n
    -- Exploding a condensed, delimited list\nWITH Tally (n) AS (\n    -- Restrict the rows in our 0-based Tally table to only what we need using TOP\n    SELECT 0 UNION ALL\n    SELECT TOP ((\n            SELECT MAX(EndPkgNo)-MIN(StartPkgNo)\n            FROM #ConsHistoryDelimited)\n        ) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))\n    FROM sys.all_columns a CROSS JOIN sys.all_columns b)\nSELECT ConsignmentNo, PkgNo=n\nFROM #ConsHistoryDelimited a\n-- First CA to split the condensed list on the comma delimiter\nCROSS APPLY dbo.DelimitedSplit8K(CondensedPkgNos, ',') b\n-- Now retrieve the range or make the single entry into a range\nCROSS APPLY (\n    SELECT StartPkgNo=LEFT(Item, CHARINDEX('-', Item + '-') - 1)\n        ,EndPkgNo=CASE CHARINDEX('-', Item) \n                        WHEN 0 THEN Item \n                        ELSE RIGHT(Item, LEN(Item) - CHARINDEX('-', Item))\n                        END\n    ) c\n-- Apply our Tally table to the range (the resulting n is our PkgNo)\nCROSS APPLY (\n    SELECT n=n+c.StartPkgNo\n    FROM Tally\n    WHERE n+c.StartPkgNo BETWEEN c.StartPkgNo AND c.EndPkgNo) d\nORDER BY ConsignmentNo, n;<\/pre>\n
     \tYou didn’t really think I was going to try something fancy and inefficient there, now did you? This works exactly the same whether your list is the islands or the gaps (change CondensedPkgNos to MissingPkgNos as the argument to DelimitedSplit8K if you don’t believe me). <\/p>\n
    The Final Word<\/h2>\n
     \tDisplaying a condensed delimited list can be most useful in the presentation layer of an application, particularly in cases where you may have long integer lists that you need to fit into just a small bit of screen real estate. While we don’t necessarily recommend storing them, there are ways that they can be handled and their manipulation is relatively simple. <\/p>\n
     \tIn this article, we’ve combined the following concepts in various ways throughout these examples: <\/p>\n