sql-server devide into groups

Question

I have following data

CREATE TABLE customers (ID INT, rankVal INT);
GO

INSERT INTO dbo.customers( ID, rankVal )
VALUES  ( 1, -- ID - int
          1  -- rankVal - int
          ),
          ( 1, -- ID - int
          2 -- rankVal - int
          ),
          ( 2, -- ID - int
          1  -- rankVal - int
          ),
          ( 2, -- ID - int
          2  -- rankVal - int
          ),


          ( 3, -- ID - int
          1  -- rankVal - int
          ),
          ( 3, -- ID - int
          3 -- rankVal - int
          ),
          ( 4, -- ID - int
          1  -- rankVal - int
          ),
          ( 4, -- ID - int
          3  -- rankVal - int
          );

I want to devide my customers into groups. For example costumer 1 and 2 have the exactly same rankvals, they must be in a single group, customer 3 and 4 must be in different group. Expected results are

Grp  costumerID
---- -----------
gr1  1
gr1  2
gr2  3
gr2  4

Answer 1

You could use STRING_AGG (SQL Server 2017 and above) to get all ranks in one and then DENSE_RANK to calculate group's number:

WITH cte AS (
  SELECT ID, STRING_AGG(rankVal,',') WITHIN GROUP (ORDER BY rankVal) AS s
  FROM customers
  GROUP BY ID
)
SELECT CONCAT('grp', DENSE_RANK() OVER(ORDER BY s)) AS GRP, ID AS customerID
FROM cte;

DBFiddle Demo