TSQL Stuff data

Question

I need to find a way to Group By some data in a specific way.

id  Number  FROM    TO
1   240     201710  201712
1   240     201712  201801
1   240     201801  201803
1   300     201803  201805
1   240     201805  999999

I need them grouped in this way:

id  TEXT
1   201710 - 201803: 240, 201803 - 20180: 300, 201805 - 999999: 240

My problem is to get the right grouping. It has to group row 1-3 and set FROM to 201710 and TO to 201803. But I cant group by the number because it can change later again.

Any ideas?

The only idea i have is checking the FROM with THE FROM of the next row and set it to the previous FROM if the number matches. Then i could group by id, number and FROM and take the MAX of TO. But I really dont wan't to use a Cursor.

Here a testscript:

DECLARE @Test AS table
(
    customerid int,
    Number     int,
    DateFrom   int,
    DateTo     int
);

INSERT INTO @Test
VALUES (1, 240, 201710, 201712),
       (1, 240, 201712, 201801),
       (1, 240, 201801, 201803),
       (1, 300, 201803, 201805),
       (1, 240, 201805, 999999);

SELECT t.customerid,
       t.Number,
       t.DateFrom,
       t.DateTo
FROM   @Test t;

SELECT   customerid,
         STUFF(
         (SELECT DISTINCT
                 ', ' + CONVERT(varchar(100), (t2.DateFrom)) + ' - ' + CONVERT(varchar(100), (t2.DateTo)) + ' : '
                 + CONVERT(varchar(100), (t2.Number))
          FROM   @Test t2
         FOR XML PATH('')),
         1,
         2,
         '') AS Text
FROM     @Test t
WHERE t.customerid = t2.customerid
GROUP BY t.customerid;

user_7132550 · Answer

Try this:

;WITH cte AS (
    SELECT t.customerid,
           t.Number,
           t.DateFrom,
           t.DateTo,
           ROW_NUMBER() over (order by datefrom) - ROW_NUMBER() over (partition by number order by datefrom) grpCol
    FROM   @Test t
), cte2 AS (
    SELECT customerid, 
           cast(MIN(datefrom) as varchar(10)) + ' - ' + cast(MAX(dateto) as varchar(10)) + ': ' + cast(number as varchar(10)) [text],
           MIN(datefrom) datefrom
    FROM cte c
    group by customerid, number, grpCol
)

SELECT customerid, 
       (select [text] + ', ' from cte2 where customerid = c.customerid order by datefrom for xml path('')) [text]
FROM cte2 c
GROUP BY customerid

Output:

customerid | text
1          | 201710 - 201803: 240, 201803 - 201805: 300, 201805 - 999999: 240,

TSQL Stuff data

1 Answers