如何编写SQL Server查询将关联记录展示在同一行

需要将tblName中的记录与关联的tblTask记录合并到同一行，通过NID字段建立关联。以下是两种可行的实现方案：

测试数据准备 #

先创建并插入示例数据，方便验证查询效果：

CREATE TABLE tblName (
    NID INT,
    Name VARCHAR(50)
);

INSERT INTO tblName VALUES
(1, 'John Doe'),
(2, 'Jane Doe');

CREATE TABLE tblTask (
    TID INT,
    Task VARCHAR(50),
    EstMin INT,
    NID INT
);

INSERT INTO tblTask VALUES
(1, 'Fix the bed', 45, 1),
(2, 'Clean room', 30, 1),
(3, 'Wipe the floor', 20, 1),
(4, 'Cook breakfast', 45, 2),
(5, 'Clean the dishes', 30, 2);

方案一：固定列查询（已知最大任务数） #

如果提前明确每个NID关联的任务数量上限（比如示例中最多3个），可以使用条件聚合实现：

SELECT
    n.NID,
    n.Name,
    MAX(CASE WHEN rn = 1 THEN t.Task END) AS Task1,
    MAX(CASE WHEN rn = 1 THEN t.EstMin END) AS EstMin1,
    MAX(CASE WHEN rn = 2 THEN t.Task END) AS Task2,
    MAX(CASE WHEN rn = 2 THEN t.EstMin END) AS EstMin2,
    MAX(CASE WHEN rn = 3 THEN t.Task END) AS Task3,
    MAX(CASE WHEN rn = 3 THEN t.EstMin END) AS EstMin3
FROM
    tblName n
LEFT JOIN (
    -- 给每个NID下的任务按TID排序生成行号
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY NID ORDER BY TID) AS rn
    FROM tblTask
) t ON n.NID = t.NID
GROUP BY
    n.NID, n.Name
ORDER BY
    n.NID;

方案二：动态列查询（任务数量不固定） #

如果任务数量不确定或可能变化，使用动态SQL自动生成对应列：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 动态生成列名集合（Task1, EstMin1, Task2, EstMin2...）
WITH TaskRows AS (
    SELECT DISTINCT
        ROW_NUMBER() OVER (PARTITION BY NID ORDER BY TID) AS rn
    FROM tblTask
)
SELECT @cols = STRING_AGG(
    QUOTENAME('Task' + CAST(rn AS VARCHAR)) + ', ' + QUOTENAME('EstMin' + CAST(rn AS VARCHAR)),
    ', '
)
FROM TaskRows;

-- 构建并执行动态查询
SET @query = '
SELECT
    n.NID,
    n.Name,
    ' + @cols + '
FROM
    tblName n
LEFT JOIN (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY NID ORDER BY TID) AS rn
    FROM tblTask
) t ON n.NID = t.NID
GROUP BY
    n.NID, n.Name
ORDER BY
    n.NID;
';

EXEC sp_executesql @query;

两种方案都能输出你期望的结果，动态列方案更灵活，适合任务数量动态变化的场景。

内容的提问来源于stack exchange，提问作者Jaime Dolor jr.