久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

合并sql中的列

Merge columns in sql(合并sql中的列)
本文介紹了合并sql中的列的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!

問題描述

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

正如您所看到的,除了 TitleNote 列之外,所有列都完全相同,是否可以合并 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)!

【網(wǎng)站聲明】本站部分內容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯(lián)系我們刪除處理,感謝您的支持!

相關文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個子標記轉換為具有多個分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個表創(chuàng)建視圖?)
Create calculated value based on calculated value inside previous row(根據(jù)前一行內的計算值創(chuàng)建計算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對?) - IT屋-程序員軟件開發(fā)技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉換為日期/月份編號(問題和答案的組合))
主站蜘蛛池模板: 久久成人一区 | 日韩一区二区三区在线 | 一级特黄色毛片 | 99精品久久久久久久 | 色综合天天网 | 国产91精品久久久久久久网曝门 | 91国产在线视频在线 | 一区二区精品 | 91婷婷韩国欧美一区二区 | 欧美久久电影 | 日韩精品在线播放 | 日本理论片好看理论片 | 成人做爰69片免费观看 | 97碰碰碰 | 亚洲天堂999 | 国产欧美一区二区三区日本久久久 | 亚洲精品成人av久久 | 91大神新作在线观看 | 99re6在线视频精品免费 | 性一交一乱一透一a级 | 综合精品在线 | 91av导航 | 99久久久国产精品免费消防器 | 网址黄 | 国产日韩精品久久 | 可以在线看的黄色网址 | 精品国产乱码久久久久久丨区2区 | 欧美日韩国产在线 | 黑人粗黑大躁护士 | 中国一级毛片免费 | av日韩在线播放 | 久久er99热精品一区二区 | 久久男人 | 久久久精品综合 | 国产精品成人一区二区 | 亚洲区中文字幕 | 日日摸日日碰夜夜爽2015电影 | 中文字幕高清 | 久久久www成人免费精品 | 午夜影院在线观看视频 | 999久久久免费精品国产 |