Can I group a string combination?

Question

I have a result that gives me a range of values ​​for a query from my database:

Start        End
-----        ---
    1          3
    5          6
  137        139      

From this, I need to query the database for records in this range that can return something like:

Id    Name
----- ------
    1 foo
    2 bar
    3 baz

Id    Name
----- ------
    5 foo
    6 baz

Id    Name
----- ------
  137 foo
  138 bar
  139 baz

I want to group the result while preserving any of the ranges of identifiers, since they correlate with the same. For example, 1-3 is the same as 137-139, so it will have a score of 2, but, of course, the "range" can be either from 2:

RangeStart   RangeEnd   Count
----------   --------   -----
       137        139       2
         5          6       1

Also note that the order should change the grouping, so foo / bar / baz does not match foo / baz / bar.

How can I do that?

EDIT: (, ), (RangeStart, RangeEnd, Count). , .

Answer 1

:

• . .
• .

:

DECLARE @Tags TABLE ( 
  TagID INT, 
  Tag   VARCHAR(3) 
)  

INSERT @Tags  
SELECT 1, 'Foo' UNION ALL 
SELECT 2, 'Bar' UNION ALL 
SELECT 3, 'Baz' UNION ALL 
SELECT 4, 'Foo' UNION ALL 
SELECT 5, 'Bar' UNION ALL
SELECT 6, 'Baz'

DECLARE @Ranges TABLE ( 
  StartRange INT,  
  EndRange   INT 
) 

INSERT @Ranges 
SELECT 1,3 UNION ALL 
SELECT 2,3 UNION ALL  
SELECT 3,4 UNION ALL 
SELECT 4,6

:

/* Get the first start and end ranges with a match and */
/* the total number of occurences of that match        */
SELECT
  StartRange,
  EndRange,
  Total
FROM (
  SELECT
    StartRange,
    EndRange,  
    Csv,
    ROW_NUMBER() OVER (PARTITION BY Csv ORDER BY StartRange ASC)  AS RowNum,
    ROW_NUMBER() OVER (PARTITION BY Csv ORDER BY StartRange DESC) AS Total
  FROM ( 
    /* For each range and its associated Tag values, */ 
    /* Concatenate the tags together using FOR XML   */ 
    /* and the STUFF function                        */ 
    SELECT 
      StartRange, 
      EndRange, 
      ( 
      SELECT STUFF(   
      (SELECT ',' + Tag 
      FROM @Tags WHERE TagID BETWEEN r.StartRange AND r.EndRange 
      ORDER BY TagID 
      FOR XML PATH('')),1,1,'')  
      ) AS Csv 
    FROM @Ranges r
  ) t1 
) t2
WHERE RowNum = 1
ORDER BY StartRange, EndRange

/* Results */

StartRange  EndRange    Total
----------- ----------- -----
1           3           2
2           3           1
3           4           1

:

/* Get the concatenated tags and their respective totals */ 
SELECT
  Csv,
  COUNT(*) AS Total
FROM ( 
  /* For each range and its associated Tag values, */ 
  /* Concatenate the tags together using FOR XML   */ 
  /* and the STUFF function                        */ 
  SELECT 
    StartRange, 
    EndRange, 
    ( 
    SELECT STUFF(   
    (SELECT ',' + Tag 
    FROM @Tags WHERE TagID BETWEEN r.StartRange AND r.EndRange 
    ORDER BY TagID 
    FOR XML PATH('')),1,1,'')  
    ) AS Csv 
  FROM @Ranges r 
) t1 
GROUP BY Csv
ORDER BY Csv

/* Results */

Csv          Total
------------ -----------
Bar,Baz      1
Baz,Foo      1
Foo,Bar,Baz  2