問題描述
我正在使用 SQL Server 2017 有一個存儲過程,其中我有一個簡單的選擇和連接,例如:
I'm using SQL Server 2017 have a stored procedure where I have a simple select with joins like:
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 以獲得一列而不是兩列?, 示例:
As you can see all columns are exactly the same except for Title
and Note
columns, is it possible to merge Title and Note to get only one column instead two?, Example :
+-----------+----------+--------------+------------+--------------------------------------------------------------+--------+--+
| 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 行合并,我做錯了什么?問候
But it just concat title
column with note
column but it no merge row 1 with row 2, what am I doing wrong? Regards
推薦答案
使用 concat()
然后 string agg()
:
https://dbfiddle.uk/?rdbms=sqlserver_206fiddle=69806c24356e5ef86fd0bfa7a239c82b一個>
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=69806c24356e5ef86fd0bfa7a239c82b
如果您不想要字符串中最后一個值的 /n
,您可以這樣做:
Edit 1:
If you don't want the /n
for the last value in the string, you can do:
select left(string,len(string)-3)從 (SELECT STRING_AGG(CONCAT(Title, ': ', Note, ' \n'),', ') 作為字符串從測試) t
如果您有多個工作編號并且不希望所有值匯總到一行,您可以執(zhí)行以下操作:
Edit 2: If you have multiple job numbers and don't want all values to aggregate to one row, you can do:
select left(string,len(string)-3)從 (SELECT STRING_AGG(CONCAT(Title, ': ', Note, ' \n'),', ')WITHIN GROUP (ORDER BY JobNumber) 作為字符串從測試按作業(yè)編號分組) t
這篇關于合并sql中的列的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網(wǎng)!