MS SQL Server Build coalesce string with loop from table

I have a table I need to build a comma separated string from

This is the result I need
1-5,9,12,15-17

From this list example

1
2
3
4
5
9
12
15
16
17
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Do an islands and gap search, then create your result.

Using window functions makes it pretty simple:

DECLARE @List TABLE
    (
        n INT
    );

INSERT INTO @List ( n )
VALUES ( 1 ) ,
       ( 2 ) ,
       ( 3 ) ,
       ( 4 ) ,
       ( 5 ) ,
       ( 9 ) ,
       ( 12 ) ,
       ( 15 ) ,
       ( 16 ) ,
       ( 17 );

WITH Starts
AS ( SELECT L.n ,
            IIF(L.n - ISNULL(LAG(L.n, 1, NULL) OVER ( ORDER BY L.n ASC ), -9999) > 1, 1, 0) AS newIsland
     FROM   @List L ) ,
     Numbered
AS ( SELECT S.n ,
            SUM(S.newIsland) OVER ( ORDER BY S.n ASC ) AS islandNo
     FROM   Starts S ) ,
     Aggregated
AS ( SELECT   CAST(MIN(N.n) AS VARCHAR(255)) AS islandStart ,
              CAST(MAX(N.n) AS VARCHAR(255)) AS islandEnd ,
              MAX(N.n) - MIN(N.n) + 1 AS islandSize ,
              N.islandNo
     FROM     Numbered N
     GROUP BY N.islandNo )
SELECT STUFF((   SELECT   ', ' + IIF(A.islandSize = 1, A.islandStart, A.islandStart + '-' + A.islandEnd)
                 FROM     Aggregated A
                 ORDER BY A.islandNo
                 FOR XML PATH('')) ,
             1 ,
             2 ,
             '') AS islands;

Open in new window

0
 
Scott PletcherSenior DBACommented:
Itzik Ben-Gan addresses this in his "islands" SQL examples.

If you can provide usable sample data -- CREATE TABLE, INSERT statements -- rather than just a list of values, I can provide a good answer based on an "islands" solution :-).
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks.

This is my final result function

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- ================================================================
-- Author:            Larry Brister
-- Create date: 01.15.2018
-- Description:      Get "scaled" list from comma seperated string
-- select dbo.fncReturnScaledString('7654,7653,7650,7604,7605,7606,')
-- ================================================================
ALTER FUNCTION fncReturnScaledString
    (
        -- Add the parameters for the function here
        @NumList VARCHAR(MAX)
    )
RETURNS VARCHAR(1000)
AS
    BEGIN
      DECLARE @ResultVar VARCHAR(1000);
        -- Declare the return variable here
        DECLARE @List TABLE
            (
                n INT
            );

        INSERT INTO @List ( n )
                    SELECT   [str]
                    FROM     dbo.ParseList(@NumList, ',')
                    ORDER BY [str];
        WITH Starts
        AS ( SELECT L.n ,
                    IIF(L.n - ISNULL(LAG(L.n, 1, NULL) OVER ( ORDER BY L.n ASC ), -9999) > 1, 1, 0) AS newIsland
             FROM   @List L ) ,
             Numbered
        AS ( SELECT S.n ,
                    SUM(S.newIsland) OVER ( ORDER BY S.n ASC ) AS islandNo
             FROM   Starts S ) ,
             Aggregated
        AS ( SELECT   CAST(MIN(N.n) AS VARCHAR(255)) AS islandStart ,
                      CAST(MAX(N.n) AS VARCHAR(255)) AS islandEnd ,
                      MAX(N.n) - MIN(N.n) + 1 AS islandSize ,
                      N.islandNo
             FROM     Numbered N
             GROUP BY N.islandNo )
        SELECT @ResultVar =  STUFF((   SELECT   ', ' + IIF(A.islandSize = 1, A.islandStart, A.islandStart + '-' + A.islandEnd)
                         FROM     Aggregated A
                         ORDER BY A.islandNo
                         FOR XML PATH('')) ,
                     1 ,
                     2 ,
                     '');
        -- Return the result of the function
        RETURN @ResultVar;
    END;
0
 
ste5anSenior DeveloperCommented:
Contains ParseList() a sanity check? Also consider using VARCHAR(MAX) as result type, cause from MAX as input you may get a result longer than 1000 characters.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.