合并sql中的列

我正在使用 SQL Server 2017 有一个存储过程，其中我有一个带有连接的简单选择，例如：

SELECT
      [p].[legacyKey] AS JobNumber
    , [p].[Name] AS JobName
    , [G].[Label] AS DesignStatus
    , [GS].[Description]  AS ShopStatus
    , [JN].Title
    , [JN].Note
    , 'Remove' AS [Remove]
      FROM [Project] AS [P]
          INNER JOIN [Customer] AS [c] ON [P].[CustomerSoldById] = [C].[CustomerKey]
          INNER JOIN [General] AS [G] ON [P].[StatusKey] = [G].[GeneralKey]
          INNER JOIN [General] AS [GS] ON [P].[ShopsStatus] = [GS].[GeneralKey]
          INNER JOIN ProjectDesign AS PD ON P.ProjectKey = PD.ProjectKey
          INNER JOIN DESIGN AS D ON PD.DesignKey = D.DesignKey
          INNER JOIN JobNotes AS JN ON PD.DesignKey = JN.DesignKey
      WHERE [G].[Extended] = 'Project Status'
      and p.LegacyKey = 18213

该查询的结果是：

+-----------+----------+--------------+------------+--------+-------------------+--------+
| JobNumber | JobNAme  | DesignStatus | ShopStatus | Title  |       Note        | Remove |
+-----------+----------+--------------+------------+--------+-------------------+--------+
|      1234 | TestName | Correct      | Inc        | Title1 | Note test design  | Remove |
|      1234 | TestName | Correct      | Inc        | Title2 | note test proyect | Remove |
+-----------+----------+--------------+------------+--------+-------------------+--------+

正如您所看到的，除了Title and Note列，是否可以合并标题和注释以仅获得一列而不是两列？，示例：

+-----------+----------+--------------+------------+--------------------------------------------------------------+--------+--+
| JobNumber | JobNAme  | DesignStatus | ShopStatus |                             Note                             | Remove |  |
+-----------+----------+--------------+------------+--------------------------------------------------------------+--------+--+
|      1234 | TestName | Correct      | Inc        | Title1 : Note test design \n , Title2 : note test proyect \n | Remove |  |
+-----------+----------+--------------+------------+--------------------------------------------------------------+--------+--+

I try

CONCAT([JN].[Title], ': ', STRING_AGG([JN].[Note], '\N'))

但它只是连接title列与note列，但它没有将第 1 行与第 2 行合并，我做错了什么？问候

Use concat() then string agg():

编辑1： 如果你不想要/n对于字符串中的最后一个值，您可以执行以下操作：

select left(string,len(string)-3) from ( SELECT STRING_AGG(CONCAT(Title, ': ', Note, ' \n'),', ') as string from test ) t

编辑2： 如果您有多个职位编号并且不希望所有值聚合到一行，您可以执行以下操作：

select left(string,len(string)-3) from ( SELECT STRING_AGG(CONCAT(Title, ': ', Note, ' \n'),', ') WITHIN GROUP (ORDER BY JobNumber) as string from test group by JobNumber ) t