問題描述

我正在使用 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)！