CODEWITHSUNDEEP
sqlt-sql
esac
esac

Reputation: 24685

Is it possible to group by a combination of rows?

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

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

From those, I need to query the database for the records in that range, which might 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 of those, keeping any of the id ranges since they correlate to the same thing. For example, 1-3 is the same as 137-139, so it would have a count of 2, but of course, the 'range' can be either of the 2:

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

Also note that the order should change the grouping, so foo/bar/baz is not the same as foo/baz/bar.

How can this be accomplished?

EDIT: I have the beginning result (start,end) and I only care about the end result (RangeStart,RangeEnd,Count). I don't actually need the intermediate results, I just use them as explanation.

Upvotes: 3

Views: 273

Answers (1)

8kb
8kb

Reputation: 11406

Here are two queries:

  • The first one concatenates the strings into groups based on the ranges and then shows the first range for each group of strings. It also has the total number of times the string appeared.
  • The second one shows the concatenated strings and their respective totals.

Setup: 

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

Query To Show First Ranges and Results: 

/* 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

Query to show concatenanted strings and totals:

/* 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

String concatentation method courtesy of Jeremiah Peschka

Upvotes: 3

Related Questions