Вопрос: Как конкатенировать текст из нескольких строк в одну текстовую строку на SQL-сервере?


Рассмотрим таблицу базы данных, содержащую имена, с тремя строками:

Peter
Paul
Mary

Есть ли простой способ превратить это в одну строку Peter, Paul, Mary?


1459


источник


Ответы:


Если вы находитесь на SQL Server 2017 или Azure, см. Матье Ренда ответ ,

У меня была аналогичная проблема, когда я пытался присоединиться к двум таблицам с отношениями «один ко многим». В 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

1085



Этот ответ может возвращать неожиданные результаты, когда присутствует предложение ORDER BY. Для получения согласованных результатов используйте один из методов FOR XML PATH, подробно описанный в других ответах.

использование COALESCE:

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

Просто какое-то объяснение (поскольку этот ответ, кажется, получает относительно регулярные взгляды):

  • Coalesce - действительно просто полезный чит, который выполняет две вещи:

1) Нет необходимости инициализировать @Namesс пустым строковым значением.

2) Не нужно снимать дополнительный разделитель в конце.

  • Вышеприведенное решение даст неверные результаты, если строка имеет НОЛЬ Значение имени (если есть НОЛЬ , НОЛЬ сделаю @Names НОЛЬ после этой строки, а следующая строка снова начнется как пустая строка. Легко фиксируется одним из двух решений:
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

В зависимости от того, какое поведение вы хотите (первый вариант просто фильтрует НОЛЬ s, второй вариант сохраняет их в списке с сообщением маркера [замените «N / A» на все, что подходит вам]).


883



Один из методов, еще не XML data()в MS SQL Server:

Предположим, что таблица с именем NameList имеет один столбец с именем FName,

SELECT FName + ', ' AS 'data()' 
FROM NameList 
FOR XML PATH('')

возвращает:

"Peter, Paul, Mary, "

Должна быть решена только дополнительная запятая.

Редактировать: Как принято из комментария @ NReilingh, вы можете использовать следующий метод для удаления конечной запятой. Предполагая одинаковые имена таблиц и столбцов:

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands

294



В 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

вы можете использовать Для синтаксиса 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   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

Это будет работать, даже ваши данные содержат недопустимые символы XML

'}}, {" ":" 'безопасно, потому что, если у вас есть данные' "}, {" ":" ', он будет сбежать до "}, {\" _ \ ": \"

Вы можете заменить ',' на любой разделитель строк


А в SQL Server 2017 база данных Azure SQL

Вы можете использовать новый Функция STRING_AGG


208



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;

156



In MySQL there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:

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

98



Use COALESCE - Learn more from here

For an example:

102

103

104

Then write below code in 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

Output would be:

102,103,104

52



Oracle 11g Release 2 supports the LISTAGG function. Documentation 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.

Warning

Be careful implementing this function if there is possibility of the resulting string going over 4000 characters. It will throw an exception. If that's the case then you need to either handle the exception or roll your own function that prevents the joined string from going over 4000 characters.


42



Postgres arrays are awesome. Example:

Create some test data:

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)

Aggregate them in an array:

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

Convert the array to a comma delimited string:

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

DONE

Since PostgreSQL 9.0 it is even easier.


41