sql - How to user grouping sets as keys for the next grouping

Question

We have the following table:

DECLARE @UserGroup TABLE (UserId int, GroupId int)

insert into @UserGroup values (1,1) -- User 1 belongs to Group 1
insert into @UserGroup values (1,2) -- User 1 belongs to Group 2
insert into @UserGroup values (2,1) -- User 2 belongs to Group 1
insert into @UserGroup values (2,2) -- User 2 belongs to Group 2
insert into @UserGroup values (3,1) -- User 3 belongs to Group 1
insert into @UserGroup values (4,4) -- User 4 belongs to Group 4

We can see that User 1 and User 2 belong to the same two groups Group 1 and Group 2. User 3 belongs to Group 1 only and User 4 belongs to Group 4. What I'm trying to achieve should look like something below:

Key(group combination) | User Id
(Group1,Group2)        | Either User 1 or User 2
(Group1)               | User 3 only
(Group4)               | User 4 only

Basically, for each combination of groups we have users for, I want to select ANY user from that group. Just to give more context, I need to select one user from a combination of groups to do some testing related to security since security is set for groups and not users.

I believe the following query does the job:

;WITH groupKeysAndUsers as
(
    select 
        STRING_AGG(ug1.GroupId, ',') WITHIN GROUP (ORDER BY ug1.GroupId) as GroupKey, 
        ug1.UserId
    from @UserGroup ug1
    group by ug1.UserId
)
select GroupKey, min(UserId) -- I use min to select any user from the grouping set
from groupKeysAndUsers
group by GroupKey

I wonder if there is a better way(faster and/or easier to consume) to achieve the same result without using STRING_AGG.

标签： sqlsql-servertsql