如何将SELECT结果集作为SELECT列？无需透视表实现关联数据嵌套

我有两张表：Job（包含ID、Name等字段）和Address（包含ID、Job_ID、Name等字段），想要获取如下格式的嵌套JSON查询结果：

[
  {
    "Job_ID": 1,
    "JobName": "Test",
    "Addresses": [
     {
       "ID": 1,
       "Name": "King street"
     },
     {
       "ID": 2,
       "Name": "Queen`s street"
     }
     ]
  }
]

目前我的查询语句只能获取每个Job对应的单条地址，语句如下：

SELECT TOP 100 
JO.ID,
JO.Closed as Deleted,
JO.Number as JobNumber,
JO.Name as JobName,
Convert(date, JO.Start_Date) as Start_Date,
JO.Job_Status_ID as Status,
A.ID as Address_ID,
A.Name as Name,
A.Number as Number,
A.Sort_Name as Sort_Name,
A.Address_1 as Address_1,
A.Address_2 as Address_2,
A.ZipCode as ZIP,
A.E_Mail_Address as Email,
A.Web_Site_URL as Web_Site_URL,
A.TAXRATE as Tax_Rate,
A.State
FROM Job JO
INNER JOIN Address A ON A.Job_Id = JO.ID

请问能否不使用透视表实现该需求？

当然可以，不用透视表的话，你可以利用SQL Server的FOR JSON PATH特性生成嵌套JSON结构，通过指定别名路径来实现层级嵌套。

修改后的查询语句如下：

SELECT TOP 100
    JO.ID AS [Job_ID],
    JO.Name AS [JobName],
    JO.Closed AS [Deleted],
    JO.Number AS [JobNumber],
    CONVERT(date, JO.Start_Date) AS [Start_Date],
    JO.Job_Status_ID AS [Status],
    -- 子查询生成当前Job对应的地址数组
    (
        SELECT
            A.ID,
            A.Name,
            A.Number,
            A.Sort_Name,
            A.Address_1,
            A.Address_2,
            A.ZipCode AS ZIP,
            A.E_Mail_Address AS Email,
            A.Web_Site_URL,
            A.TAXRATE AS Tax_Rate,
            A.State
        FROM Address A
        WHERE A.Job_Id = JO.ID
        FOR JSON PATH
    ) AS [Addresses]
FROM Job JO
-- 用INNER JOIN过滤掉无地址的Job，如需保留无地址Job改为LEFT JOIN
INNER JOIN Address A ON A.Job_Id = JO.ID
GROUP BY JO.ID, JO.Name, JO.Closed, JO.Number, JO.Start_Date, JO.Job_Status_ID
FOR JSON PATH;

核心说明： #

• 内层子查询通过FOR JSON PATH生成对应Job的地址数组，外层查询将其作为Addresses字段嵌入结果。
• GROUP BY用于确保每个Job仅返回一条记录，避免重复的Job条目。
• 默认生成的结果就是你需要的数组嵌套结构，若不需要最外层数组，可添加WITHOUT_ARRAY_WRAPPER参数。

内容的提问来源于stack exchange，提问作者Дидар Темирханов