时间戳

首页 > 解决方案 > 合并sql中的列

sql - 合并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如您所见，除了和列之外，所有列都完全相同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 行合并，我做错了什么？问候

标签： sqlsql-servertsql

解决方案

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

推荐阅读