values - t sql concat group by




如何將多行中的文本連接成SQL服務器中的單個文本字符串? (20)

SQL Server 2005中

SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

在SQL Server 2016中

您可以使用FOR JSON語法

SELECT per.ID,
Emails = JSON_VALUE(
   REPLACE(
     (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._'
) 
FROM Person per

結果會變成

Id  Emails
1   [email protected]
2   NULL
3   [email protected], [email protected]

即使您的數據包含無效的XML字符,這也可以工作

''},{“ ”:''是安全的,因為如果你的數據包含'“},{” “:”',它將被轉移到“},{\”_ \“:\”

您可以用任何字符串分隔符替換','

並且在SQL Server 2017中,Azure SQL數據庫

您可以使用新的STRING_AGG功能

考慮一個包含三行的數據庫表:

Peter
Paul
Mary

有沒有簡單的方法可以把它變成Peter, Paul, Mary的單串?


SQL Server 2017和SQL Azure:STRING_AGG

從SQL Server的下一個版本開始,我們可以最終在行之間進行連接,而無需訴諸任何變量或XML witchery。

沒有分組

SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;

通過分組:

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

通過分組和分類排序

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department 
GROUP BY GroupName;

Oracle 11g第2版支持LISTAGG功能。 文檔here

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

警告

如果產生的字符串可能超過4000個字符,請小心執行此功能。 它會拋出一個異常。 如果是這種情況,那麼你需要處理異常或者滾動自己的函數,以防止連接字符串超過4000個字符。


Postgres數組很棒。 例:

創建一些測試數據:

postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE                                      
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');                                                          
INSERT 0 3
test=# select * from names;
 name  
-------
 Peter
 Paul
 Mary
(3 rows)

將它們聚合在一個數組中:

test=# select array_agg(name) from names;
 array_agg     
------------------- 
 {Peter,Paul,Mary}
(1 row)

將數組轉換為以逗號分隔的字符串:

test=# select array_to_string(array_agg(name), ', ') from names;
 array_to_string
-------------------
 Peter, Paul, Mary
(1 row)

DONE

自PostgreSQL 9.0以來,它變得更加容易 。


使用COALESCE

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

只是一些解釋(因為這個答案似乎得到相對普遍的看法):

  • Coalesce真的只是一個有用的作弊,它完成了兩件事情:

1)不需要用空字符串值初始化@Names

2)最後不需要剝離額外的分離器。

  • 如果一行有一個NULL Name值,上面的解決方案將得到不正確的結果(如果有一個NULL值 ,則NULL將在該行之後使@Names NULL ,並且下一行將重新以空字符串的形式重新開始。兩種解決方案:
DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

要么:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

根據你想要的行為(第一個選項只是過濾NULL) ,第二個選項使用標記消息將它們保留在列表中[用'適合你的'替換'N / A')。


使用COALESCE - 從這裡了解更多

舉個例子:

102

103

104

然後在sql server中寫下如下代碼,

Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers 
SELECT  @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM   TableName where Number IS NOT NULL

SELECT @Numbers

輸出將是:

102,103,104

即用型解決方案,無需額外的逗號:

select substring(
        (select ', '+Name AS 'data()' from Names for xml path(''))
       ,3, 255) as "MyList"

一個空列表將導致NULL值。 通常您會將列表插入表列或程序變量中:根據需要調整255最大長度。

(Diwakar和Jens Frandsen提供了很好的答案,但需要改進。)


在MySQL中有一個函數Group_Concat ,它允許你連接多行的值。 例:

SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
FROM users 
WHERE id IN (1,2,3) 
GROUP BY a

在SQL Server 2005和更高版本中,使用下面的查詢連接行。

DECLARE @t table
(
    Id int,
    Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d' 

SELECT ID,
stuff(
(
    SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'') 
FROM (SELECT DISTINCT ID FROM @t ) t


如果你想處理空值,你可以通過添加一個where子句或者在第一個子句中添加另一個COALESCE來完成。

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People

對於Oracle數據庫,請參閱此問題: 如何在不創建存儲過程的情況下將多行連接到Oracle中的一行?

最好的答案似乎是@Emmanuel,它使用Oracle 11g第2版及更高版本中提供的內置LISTAGG()函數。

SELECT question_id,
   LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id

正如@ user762952指出的那樣,根據Oracle的文檔http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php ()函數也是一個選項。 它看起來很穩定,但Oracle明確建議不要將它用於任何應用程序SQL,因此使用時風險自擔。

除此之外,你將不得不編寫你自己的功能; 上面的Oracle文檔有關於如何做到這一點的指導。


建議使用遞歸CTE解決方案,但未提供代碼。 下面的代碼是遞歸CTE的一個例子 - 請注意,儘管結果與問題相匹配,但數據並不完全符合給定的描述,因為我假設你真的想在一組行上做這件事,而不是全部表中的行。 將其更改為匹配表中的所有行將作為讀者的練習。

;with basetable as 
(   SELECT id, CAST(name as varchar(max))name, 
        ROW_NUMBER() OVER(Partition By id     order by seq) rw, 
        COUNT(*) OVER (Partition By id) recs 
FROM (VALUES (1, 'Johnny', 1), (1,'M', 2), 
                  (2,'Bill', 1), (2, 'S.', 4), (2, 'Preston', 5), (2, 'Esq.', 6),
        (3, 'Ted', 1), (3,'Theodore', 2), (3,'Logan', 3),
                  (4, 'Peter', 1), (4,'Paul', 2), (4,'Mary', 3)

           )g(id, name, seq)
),
rCTE as (
    SELECT recs, id, name, rw from basetable where rw=1
    UNION ALL
    SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw+1
    FROM basetable b
         inner join rCTE r
    on b.id = r.id and b.rw = r.rw+1
)
SELECT name FROM rCTE
WHERE recs = rw and ID=4

從PostgreSQL 9.0開始,這很簡單:

select string_agg(name, ',') 
from names;

在9.0之前的版本中,可以使用array_agg() ,如hgmnz所示


我在家裡無法訪問SQL Server,所以我猜這裡的語法,但它或多或少:

DECLARE @names VARCHAR(500)

SELECT @names = @names + ' ' + Name
FROM Names

我真的很喜歡達納的回答 。 只是想完成它。

DECLARE @names VARCHAR(MAX)
SET @names = ''

SELECT @names = @names + ', ' + Name FROM Names 

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

為了避免空值,你可以使用CONCAT()

DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name) 
FROM Names
select @names

當我試圖用一對多關係連接兩個表時,我遇到了類似的問題。 在SQL 2005中,我發現XML PATH方法可以很容易地處理行的連接。

如果有一張名為STUDENTS的表格

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

我預期的結果是:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

我使用了以下T-SQL

Select Main.SubjectID,
       Left(Main.Students,Len(Main.Students)-1) As "Students"
From
    (
        Select distinct ST2.SubjectID, 
            (
                Select ST1.StudentName + ',' AS [text()]
                From dbo.Students ST1
                Where ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                For XML PATH ('')
            ) [Students]
        From dbo.Students ST2
    ) [Main]

如果您可以在一開始時連接逗號並使用substring跳過第一個逗號,那麼您可以以更緊湊的方式執行相同的操作,因此您不需要執行子查詢:

Select distinct ST2.SubjectID, 
    substring(
        (
            Select ','+ST1.StudentName  AS [text()]
            From dbo.Students ST1
            Where ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            For XML PATH ('')
        ), 2, 1000) [Students]
From dbo.Students ST2

這個答案在服務器上需要一些特權才能工作。

Assemblies對你來說是一個很好的選擇。 有很多網站解釋如何創建它。 我認為很好解釋的是這one

如果你想,我已經創建了程序集,並且可以在here下載DLL。

一旦你下載了它,你將需要在SQL Server中運行以下腳本:

CREATE Assembly concat_assembly 
   AUTHORIZATION dbo 
   FROM '<PATH TO Concat.dll IN SERVER>' 
   WITH PERMISSION_SET = SAFE; 
GO 

CREATE AGGREGATE dbo.concat ( 

    @Value NVARCHAR(MAX) 
  , @Delimiter NVARCHAR(4000) 

) RETURNS NVARCHAR(MAX) 
EXTERNAL Name concat_assembly.[Concat.Concat]; 
GO  

sp_configure 'clr enabled', 1;
RECONFIGURE

觀察組裝的路徑可以被服務器訪問。 由於您已成功完成所有步驟,因此可以使用以下功能:

SELECT dbo.Concat(field1, ',')
FROM Table1

希望能幫助到你!!!


DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

這讓流浪的逗號開始。

但是,如果您需要其他列或將CSV表格包含在CSV中,則需要將其包含在標量用戶定義字段(UDF)中。

您也可以在SELECT子句中使用XML路徑作為相關的子查詢(但是我必須等到我重新開始工作,因為Google不在家工作):-)





group-concat