CODEWITHSUNDEEP
sqlsql-serversequence
Wsi
Wsi

Reputation: 111

Stuff year gaps to show separte listings for gaps - SQL Server

I have a column with sequence. I need to find the gaps in sequence and run stuff to create separate listings where gap occurs. I have created a cte with min and max to use with stuff.. I can't figure out how to identify gaps in sequence though. Please assist. Here is the same data:

CREATE TABLE Table1
(
    sku varchar(50), 
    year float, 
    make varchar(50),  
    model varchar(50),
    model2 varchar(50)
);

INSERT INTO Table1 (sku, year, make, model, model2)
VALUES ('AVS1234', '2000',  'Xbox', 'pilot', 'ex'),
       ('AVS1234', '2001',  'Xbox', 'pilot', 'ex'),
       ('AVS1234', '2014',  'Xbox', 'pilot', 'ex');

WITH firstpass AS
(
    SELECT
        sku,
        MIN(year) AS min,
        MAX(year) AS max,
        make, model, model2
    FROM
        Table1
    GROUP BY
        sku, make, model, model2
)
SELECT 
    sku,
    CAST([min] AS NVARCHAR(10)) + '-' + 
    CAST([max] AS NVARCHAR(10)) + ' ' + make + ' ' + model +
    STUFF((SELECT DISTINCT ', ' + COALESCE([model2], '') + ''
           FROM firstpass 
           WHERE sku = a.sku
           FOR XML PATH (''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 1, '') AS NamesList2,
    ROW_NUMBER() OVER (PARTITION BY sku ORDER BY sku asc) AS RowNum
FROM 
    firstpass AS a
GROUP BY 
    sku, [min], [max], make, model;

DROP TABLE table1

The intended result is 

sku          Nameslist                  Row
-------------------------------------------
AVS1234      2000 - 2001 xbox Pilot ex  1
AVS1234      2014 xbox Pilot ex         2

Upvotes: 2

Views: 66

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

This is a classic Gaps and Islands with a little twist.

Example

Select SKU
      ,Nameslist = concat(
                   case when min(Year)<> max(Year) then concat(min(Year),' - ') end
                   ,max(year)
                   ,' '+max(make)
                   ,' '+max(model)
                   ,' '+max(model2)
                  )
 From (
         Select *
               ,Grp = Year - Row_Number() over (Partition by SKU order by Year)
          From  Table1
      ) A
 Group By SKU,Grp

Returns

SKU        Nameslist
AVS1234    2000 - 2001 Xbox pilot ex
AVS1234    2014 Xbox pilot ex

Upvotes: 1

Related Questions