我使用的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和Note列之外，所有列都完全相同，是否可以合并Title和Note，只得到一列而不是两列？，示例：
+-----------+----------+--------------+------------+--------------------------------------------------------------+--------+--+
| JobNumber | JobNAme | DesignStatus | ShopStatus | Note | Remove | |
+-----------+----------+--------------+------------+--------------------------------------------------------------+--------+--+
| 1234 | TestName | Correct | Inc | Title1 : Note test design n , Title2 : note test proyect n | Remove | |
+-----------+----------+--------------+------------+--------------------------------------------------------------+--------+--+
我尝试
CONCAT([JN].[Title], ': ', STRING_AGG([JN].[Note], 'N'))
但它只是将title列和note列连接起来，而没有将行1和行2合并，我做错了什么？问候
先使用concat()，然后使用string agg():
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=69806c24356e5ef86fd0bfa7a239c82b
编辑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
您可以通过使用动态查询将列"透视"到行，然后在按分组的查询中使用它来返回而不重复，从而达到预期效果。这是您的示例中的代码。我为插入表中的结果替换了您的查询，但您可以使用它来替换我的测试表，以在更多不同的结果中测试它。
CREATE TABLE test
(
JobNumber int
,JobNAme varchar(25)
,DesignStatus varchar(25)
,ShopStatus varchar(25)
,Title varchar(25)
,Note varchar(50)
,Remove varchar(25)
)
GO
INSERT INTO test(JobNumber,JobNAme,DesignStatus,ShopStatus,Title,Note,Remove)
VALUES
(1234,'TestName','Correct','Inc','Title1','Note test design','Remove')
,(1234,'TestName','Correct','Inc','Title2','Note test proyect','Remove')
GO
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + title + ': ' + Note + ' n'
FROM test
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'WITH CTE AS
(
SELECT JobNumber,JobNAme,DesignStatus,ShopStatus,'''+@Cols+''' AS Note,Remove
FROM test
)
SELECT JobNumber,JobNAme,DesignStatus,ShopStatus,NOTE,Remove
FROM CTE
GROUP BY JobNumber,JobNAme,DesignStatus,ShopStatus,NOTE,Remove '
--PRINT @query
EXEC sp_executesql @query