合并sql中的列

2021-09-10 00:00:00 sql tsql sql-server

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

如果您有多个工作编号并且不希望所有值汇总到一行，您可以执行以下操作:

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) 作为字符串从测试按作业编号分组) t

相关文章