时间戳

首页 > 解决方案 > SQL group by 组合第二组并获得不同的第一组

sql - SQL group by 组合第二组并获得不同的第一组

问题描述

查询当前返回

Bob    President     5
Joe    Programmer    3
Greg   Programmer    2
Joe    Janitor       6
Liz    Programmer    6

但我希望每个人只有一排这样

Bob    President     5
Joe    Programmer (or Janitor)  9
Greg   Programmer    2
Liz    Programmer    6

我现在按名称和角色分组。我如何也只获得不同的名称并结合该值？

SELECT
   ...
FROM 
    vw_QuickbaseAudit v
    INNER JOIN [staging].[QuickbaseSystemUsers] qsu ON v.[user] = qsu.UserId
WHERE 
    Date_Modified >= @StartDate
    AND Date_Modified < DATEADD(dd, 1, @EndDate)
GROUP BY
    qsu.UserFirstName + ' ' + qsu.UserLastName, 
    qsu.RoleName
ORDER BY 
    RoleName, 
    NumberOfEditTotal DESC;

标签： sqlsql-servergroup-by

解决方案

SELECT qsu.UserFirstName + ' ' + qsu.UserLastName AS "FullName",
    STUFF((SELECT '/' + r.RoleName FROM QuickbaseSystemUsers r on r.UserID = qsu.UserID FOR XML PATH('')), 1, 2, '') as "Roles",
    NumberOfEditTotal << [however you calced this]
FROM vw_QuickbaseAudit v
    INNER JOIN [staging].[QuickbaseSystemUsers] qsu ON v.[user] = qsu.UserId 
WHERE Date_Modified >= @StartDate AND Date_Modified < DATEADD(dd, 1, @EndDate) 
GROUP BY qsu.UserFirstName + ' ' + qsu.UserLastName, 
    STUFF((SELECT '/' + r.RoleName FROM QuickbaseSystemUsers r on r.UserID = qsu.UserID FOR XML PATH('')), 1, 2, '')
ORDER BY RoleName, NumberOfEditTotal DESC;

它的一些变体（编辑：错过了 group by 中的附加 STUFF 命令）

推荐阅读