SQL Server查询多行转单行：合并同一承运商的多职位信息

表结构 #

Assignments表 #

aID,
acID,
clientID,
userID,
pos,
dateOn

AssignmentCarriers表 #

acID,
clientID,
cName,
isAssignment

Users表 #

userID,
fName

Clients表 #

clientID,
cName,
code

问题场景 #

每个客户拥有多个承运商，每个承运商对应3个待填充的职位。现有查询能返回正确信息，但同一承运商的3个职位信息分散在不同行，需要合并为同一行展示。

当前查询语句 #

SELECT DISTINCT
    c.code,
    c.cName,
    ac.cName,
    (SELECT fname 
     FROM assignments 
     INNER JOIN users ON users.userID = assignments.userID 
     WHERE pos = 1 AND aID = a.aID) AS [User 1],
    (SELECT dateOn 
     FROM assignments 
     WHERE pos = 1 AND aID = a.aID) AS [Date Assigned 1],
    (SELECT fname 
     FROM assignments 
     INNER JOIN users ON users.userID = assignments.userID 
     WHERE pos = 2 AND aID = a.aID) AS [User 2],
    (SELECT dateOn 
     FROM assignments 
     WHERE pos = 2 AND aID = a.aID) AS [Date Assigned 2],
    (SELECT fname 
     FROM assignments 
     INNER JOIN users ON users.userID = assignments.userID 
     WHERE pos = 3 AND aID = a.aID) AS [User 3],
    (SELECT dateOn 
     FROM assignments 
     WHERE pos = 3 AND aID = a.aID) AS [Date Assigned 3]
FROM 
    clients c
INNER JOIN 
    assignments a ON a.clientID = c.clientID
INNER JOIN 
    assignmentCarriers ac ON ac.acID = a.acID
WHERE 
    isAssignment = 'True' 
    AND c.active = 'True'
ORDER BY 
    c.cName

当前返回结果 #

HDPT  Home Depot  R+L Domestic  Phil Brown  4/1/2023  Null  Null  Null  Null
HDPT  Home Depot  R+L INTL  Phil Brown  5/12/2000  Null  Null  Null  Null
HDPT  Home Depot  R+L Domestic  Null  Null  Mark Twain  1/22/15  Null  Null
HDPT  Home Depot  R+L INTL  Null  Null  Jen Gump  11/12/12  Null  Null
HDPT  Home Depot  R+L Domestic  Null  Null  Null  Null  Rob Mills  2/2/12
HDPT  Home Depot  R+L INTL  Null  Null  Null  Null  John Smith  12/2/22

期望输出结果 #

HDPT  Home Depot  R+L Domestic  Phil Brown  4/1/2023  Mark Twain  1/22/15  Rob Mills  2/2/12
HDPT  Home Depot  R+L INTL  Phil Brown  5/12/2000  Jen Gump  11/12/12  John Smith  12/2/22

解决方案：使用条件聚合实现行转列 #

针对固定数量的职位（3个），用条件聚合是最直接高效的方法，通过分组将同一承运商的多条记录合并为一行：

SELECT 
    c.code,
    c.cName,
    ac.cName AS CarrierName,
    -- 提取pos=1的用户和分配日期
    MAX(CASE WHEN a.pos = 1 THEN u.fName END) AS [User 1],
    MAX(CASE WHEN a.pos = 1 THEN a.dateOn END) AS [Date Assigned 1],
    -- 提取pos=2的用户和分配日期
    MAX(CASE WHEN a.pos = 2 THEN u.fName END) AS [User 2],
    MAX(CASE WHEN a.pos = 2 THEN a.dateOn END) AS [Date Assigned 2],
    -- 提取pos=3的用户和分配日期
    MAX(CASE WHEN a.pos = 3 THEN u.fName END) AS [User 3],
    MAX(CASE WHEN a.pos = 3 THEN a.dateOn END) AS [Date Assigned 3]
FROM 
    clients c
INNER JOIN 
    assignments a ON a.clientID = c.clientID
INNER JOIN 
    assignmentCarriers ac ON ac.acID = a.acID
INNER JOIN 
    users u ON u.userID = a.userID
WHERE 
    ac.isAssignment = 'True' 
    AND c.active = 'True'
GROUP BY 
    c.code, c.cName, ac.cName
ORDER BY 
    c.cName, ac.cName

说明 #

1. 分组逻辑：通过GROUP BY按客户编号、客户名称和承运商名称分组，确保每个承运商对应一行结果
2. 条件筛选与聚合：用CASE语句筛选对应职位（pos）的记录，再通过MAX聚合函数取出非空值（每个职位仅对应一条记录，MAX会忽略NULL，保留有效数据）
3. 性能优化：直接关联Users表替代原查询中的子查询，减少嵌套查询的性能开销

内容的提问来源于stack exchange，提问作者Damien