Collect specific values matching criteria, group consecutive values

Question

Sorry for the confusing Title, I couldn't think of a better way to work it.

I have a set of data where some of the values are consecutive numbers

lngDoffID   strProductNumber
876190      20170L
876205      20170L
876206      20170L
876207      20170L
876209      20170L
876210      20170L
876211      20170L
876212      20170L
876215      20170L
876200      20180T
876205      20180T
876206      20180T
876207      20180T
876208      20180T
876209      20180T
876210      20180T
876211      20180T    

I want a query that returns this:

strProductNumber    strDoffRange
20170L              876190
20170L              876205-876207
20170L              876209-876212
20170L              876215
20180T              876200
20180T              876205-876211

or this would be even better:

strProductNumber    strDoffRange
20170L              876190, 876205-876207, 876209-876212, 876215
20180T              876200, 876205-876211

Answer 1

This query gives the output that you need

SELECT AD.STRPRODUCTNUMBER,
       WM_CONCAT(AD.JOIN_VAL) AS JOIN_VAL
       FROM 
(SELECT A1.STRPRODUCTNUMBER,
       DECODE(TO_CHAR(A1.LGNDOFFID, '999999'),
              TO_CHAR(A2.LGNDOFFID, '999999'),
              TO_CHAR(A1.LGNDOFFID, '999999'),
              TO_CHAR(A2.LGNDOFFID, '999999') || '-' || TO_CHAR(A1.LGNDOFFID, '999999')) AS JOIN_VAL
  FROM (SELECT A.STRPRODUCTNUMBER, A.LGNDOFFID, ROWNUM AS ROW_NUM
          FROM (SELECT DISTINCT BT.STRPRODUCTNUMBER,
                                BT.LGNDOFFID,
                                DECODE((SELECT DISTINCT BT1.LGNDOFFID
                                         FROM SO_BUFFER_TABLE_5 BT1
                                        WHERE BT1.STRPRODUCTNUMBER =
                                              BT.STRPRODUCTNUMBER
                                          AND (BT1.LGNDOFFID =
                                              BT.LGNDOFFID + 1)),
                                       NULL,
                                       'FALSE',
                                       'TRUE') AS NEXT_VAL_EXIST
                  FROM SO_BUFFER_TABLE_5 BT
                 ORDER BY BT.STRPRODUCTNUMBER) A
         WHERE A.NEXT_VAL_EXIST = 'FALSE') A1,
       (SELECT A.STRPRODUCTNUMBER, A.LGNDOFFID, ROWNUM AS ROW_NUM
          FROM (SELECT DISTINCT BT.STRPRODUCTNUMBER,
                                BT.LGNDOFFID,
                                DECODE((SELECT DISTINCT BT1.LGNDOFFID
                                         FROM SO_BUFFER_TABLE_5 BT1
                                        WHERE BT1.STRPRODUCTNUMBER =
                                              BT.STRPRODUCTNUMBER
                                          AND (BT1.LGNDOFFID =
                                              BT.LGNDOFFID + 1)),
                                       NULL,
                                       'FALSE',
                                       'TRUE') AS NEXT_VAL_EXIST
                  FROM SO_BUFFER_TABLE_5 BT
                 ORDER BY BT.STRPRODUCTNUMBER) A
         WHERE DECODE((SELECT DISTINCT BT.LGNDOFFID
                        FROM SO_BUFFER_TABLE_5 BT
                       WHERE BT.STRPRODUCTNUMBER = A.STRPRODUCTNUMBER
                         AND (BT.LGNDOFFID + 1 = A.LGNDOFFID)),
                      NULL,
                      'FALSE',
                      'TRUE') = 'FALSE') A2
 WHERE A1.STRPRODUCTNUMBER = A2.STRPRODUCTNUMBER
   AND A1.ROW_NUM = A2.ROW_NUM) AD
   GROUP BY AD.STRPRODUCTNUMBER

The main query is made up of 2 sub-queries A1 & A2 A1 extracts the upper limit of the values

20170L  876190  1
20170L  876207  2
20170L  876212  3
20170L  876215  4
20180T  876200  5
20180T  876211  6

while the A2 query extract the lower limit

20170L  876190  1
20170L  876205  2
20170L  876209  3
20170L  876215  4
20180T  876200  5
20180T  876205  6

Then I have combined them to get output like this

20170L   876190
20170L   876205- 876207
20170L   876209- 876212
20170L   876215
20180T   876200
20180T   876205- 876211

I have used rownum as an ID to match corresponding values.

Lastly I have used WM_CONCAT function to combine the values to get the output like

20170L   876190, 876205- 876207, 876209- 876212, 876215
20180T   876200, 876205- 876211

Hope it helps

Answer 2

-- Sample data
;with YourTable(lngDoffID,   strProductNumber) as
(
select 876190,      '20170L' union all
select 876205,      '20170L' union all
select 876206,      '20170L' union all
select 876207,      '20170L' union all
select 876209,      '20170L' union all
select 876210,      '20170L' union all
select 876211,      '20170L' union all
select 876212,      '20170L' union all
select 876215,      '20170L' union all
select 876200,      '20180T' union all
select 876205,      '20180T' union all
select 876206,      '20180T' union all
select 876207,      '20180T' union all
select 876208,      '20180T' union all
select 876209,      '20180T' union all
select 876210,      '20180T' union all
select 876211,      '20180T'
)

-- Your query
select strProductNumber,
       stuff((select ', '+
                     cast(min(lngDoffID) as varchar(10))+
                     case when min(lngDoffID) = max(lngdoffID) 
                          then '' 
                          else '-'+cast(max(lngDoffID) as varchar(10)) 
                     end
              from ( select *,
                            row_number() over(order by strProductNumber, lngDoffID) as rn
                     from YourTable
                   ) as T
              where T.strProductNumber = Y.strProductNumber      
              group by strProductNumber, lngDoffID-rn
              order by strProductNumber, min(lngDoffID)
              for xml path('')), 1, 2, '') as strDoffRange
from YourTable as Y
group by strProductNumber

Result:

strProductNumber strDoffRange
---------------- ---------------------------------------------
20170L           876190, 876205-876207, 876209-876212, 876215
20180T           876200, 876205-876211