TSQL Get Numbers as Range or Comma delimited

Question

I'm trying to combine Numbers of Packagings, grouped on a Ordernumber. At the moment i combine this with STUFF as Comma delimited String. So i get now as example: 1-001, 1-002, 1-003

But if the Numbers are 1-001, 1-002, 1-003, 1-004, 1-007 i want get the Ranges of this Packagenumbers which are in order and this ranges (or single Packagings) comma delimited.

Example:

Packagings: 1-001, 1-002, 1-003, 1-004, 1-007, 1-008, 2-001
Expected: 1-001 to 1-004, 1-007 to 1-008, 2-001

My current Method is something like this:

SELECT
    o.OrderNumber 
    , op.PositionNumber
    , STUFF((
        SELECT ',' + ifp.Packnumber
        FROM FilledPackage ifp
        INNER JOIN PositionInFIlledPackage pifp
        ON ifp.FilledPackageId = pifp.FilledPackageId
        WHERE 
            ifp.OrderNumber = o.OrderNumber
            AND pifp.PositionNumber = op.PositionNumber
        ORDER BY ifp.Code, ifp.PackOfTypeCounter
        FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
      ,1,1,'') AS Packagings
FROM MyOrder o
INNER JOIN MyPosition op
ON o.OrderId = op.OrderId
GROUP BY 
    o.OrderNumber 
    , op.PositionNumber

• ifp.Code is the Packaging Type (Like the 1 in 1-00x)
• ifp.PackOfTypeCounter is the current Packagenumber from a type (like the 1 in x-001)
• The PackNumber is a String like 1-002 (1 => Package Type, 2 => PackageNumber) Example: PackNumber = ifp.Code + "-" + ifp.PackOfTypeCounter

Someone has a idee how this is possible?

At the end i want have something like this:

Position 1: Pack: 4-001 to 4-008
Position 2: Pack: 1-001 to 1-004, 2-001 to 2-002, 4-009
Position 3: Pack: 4-010
Position 3: Pack: 1-005 to 1-007, 4-011

From a view like this:

• OrderNumber
• PositionNumber
• PackagingCode (int) => PackageType => 1
• PackOfTypeCounter (int) => 001
• FullPackageNumber (1-001)

thanks for your help

Answer 1

You're asking a lot, let's focus first on this requirement:

Packagings: 1-001, 1-002, 1-003, 1-004, 1-007, 1-008, 2-001
Expected: 1-001 to 1-004, 1-007 to 1-008, 2-001

With DelimitedSplit8K, and some basic knowledge about resolving gaps & islands this is somewhat easy:

DECLARE @packagings VARCHAR(1000) = '1-001, 1-002, 1-003, 1-004, 1-007, 1-008, 2-001';

SELECT STUFF(
(
  SELECT 
    ', '+
    CASE 
      WHEN MIN(g.txt) = MAX(g.txt) 
      THEN CAST(g.txtGroup AS VARCHAR(100))+'-'+CAST(MIN(g.txt) AS VARCHAR(100)) 
      ELSE CAST(g.txtGroup AS VARCHAR(100))+'-'+CAST(MIN(g.txt) AS VARCHAR(100))+' to '+
           CAST(g.txtGroup AS VARCHAR(100))+'-'+CAST(MAX(g.txt) AS VARCHAR(100))
    END
  FROM
  (
    SELECT txtGroup = t1.txt, t2.txt, grouper = t2.txt - split.ItemNumber
    FROM dbo.DelimitedSplit8K(REPLACE(@packagings,' ',''),',') AS split
    CROSS APPLY (VALUES(CHARINDEX('-', split.item)))           AS mid(pos)
    CROSS APPLY (VALUES(SUBSTRING(split.item,1,mid.pos-1)))    AS t1(txt)
    CROSS APPLY (VALUES(SUBSTRING(split.item,mid.pos+1,8000))) AS t2(txt)
  ) g
  GROUP BY g.txtGroup, g.grouper
  ORDER BY g.txtGroup
  FOR XML PATH('')
),1,2,'');

Returns:

1-001 to 1-004, 1-007 to 1-008, 2-001