sql - 'string_agg' is not a recognized built-in function name.




如何将多行中的文本连接成SQL服务器中的单个文本字符串? (20)

考虑一个包含三行的数据库表:

Peter
Paul
Mary

有没有简单的方法可以把它变成Peter, Paul, Mary的单串?


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功能


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以来,它变得更加简单 。


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

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

从PostgreSQL 9.0开始,这很简单:

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

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


使用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

当我试图用一对多关系连接两个表时,我遇到了类似的问题。 在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

我真的很喜欢达纳的回答 。 只是想完成它。

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

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

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

我通常使用select来连接SQL Server中的字符串:

with lines as 
( 
  select 
    row_number() over(order by id) id, -- id is a line id
    line -- line of text.
  from
    source -- line source
), 
result_lines as 
( 
  select 
    id, 
    cast(line as nvarchar(max)) line 
  from 
    lines 
  where 
    id = 1 
  union all 
  select 
    l.id, 
    cast(r.line + N', ' + l.line as nvarchar(max))
  from 
    lines l 
    inner join 
    result_lines r 
    on 
      l.id = r.id + 1 
) 
select top 1 
  line
from
  result_lines
order by
  id desc

这也是有用的

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

回报

Peter,Paul,Mary

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