GROUP BY - SQL Server

Question

我如何分组列user_type和角色描述

id  USER_NAME   USER_TYPE   ROLE_DESCRIPTION
40406   AJ     PROGRAMMER2  Reversal
40406   AJ     PROGRAMMER2  Requester
40406   AJ     PROGRAMMER2  Programmer
40406   AJ     REQUESTER    Reversal
40406   AJ     REQUESTER    Requester
40406   AJ     REQUESTER    Programmer

当前我有此查询，但是它给我错误的"列"'user.id'在选择列表中无效，因为它不包含在汇总函数或croups子句中。"：

select U.id,
        U.USER_NAME,
        UT.USER_TYPE,
        R.ROLE_DESCRIPTION
 from USERS U
 left join USERS_USER_TYPE UUT on U.id = UUT.CON_ID
 left join USER_TYPE UT on UUT.USER_TYPE_ID = UT.ID
 left join USER_ROLE UR on U.id = UR.CON_ID
 left join ROLES R on UR.ROLE_ID = R.ID
 where U.id = 40406
 group by UT.ID

预期输出：

id  USER_NAME   USER_TYPE   ROLE_DESCRIPTION
40406   AJ     PROGRAMMER2  Reversal
40406   AJ     REQUESTER    Requester
40406   AJ     NULL         Programmer

Answer 1

对于非相关数据表示，我将随机数添加到每列的不同数据中，然后根据此随机数组合这些列。我使用DENSE_RANk函数将随机编号提供到列的不同数据。我对此任务的解决方案是 -

;with cte as
(
    select U.id,
        U.USER_NAME,
        UT.USER_TYPE,
        R.ROLE_DESCRIPTION
    from USERS U
    left join USERS_USER_TYPE UUT on U.id = UUT.CON_ID
    left join USER_TYPE UT on UUT.USER_TYPE_ID = UT.ID
    left join USER_ROLE UR on U.id = UR.CON_ID
    left join ROLES R on UR.ROLE_ID = R.ID
    where U.id = 40406
    group by UT.ID
)
select
    isnull(x.id, y.id) as id,
    isnull(x.USER_NAME, y.USER_NAME) as user_name,
    x.USER_TYPE,
    y.ROLE_DESCRIPTION
from
    (select distinct
        dense_rank() over (order by user_type) as indexid,
        id, User_name, user_type
        from cte) as x
    full join
    (select distinct
        dense_rank() over (order by role_description) as indexid,
        id, User_name, ROLE_DESCRIPTION
        from cte) as y
    on y.indexid = x.indexid

输出 -

id      USER_NAME   USER_TYPE   ROLE_DESCRIPTION
40406   AJ          PROGRAMMER2 Programmer
40406   AJ          REQUESTER   Requester
40406   AJ          NULL        Reversal