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

avatar
JohnnyM
11 октября 2008 в 23:49
2686080
48
2113

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

Peter
Paul
Mary

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

Источник
Matt Hamilton
12 октября 2008 в 00:03
27

Чтобы получить ответы, относящиеся к SQL Server, попробуйте этот вопрос.

Pykler
6 мая 2011 в 19:48
20

Для MySQL проверьте Group_Concat из этот ответ

Pete Alvin
2 октября 2014 в 11:47
28

Я бы хотел, чтобы следующая версия SQL Server предлагала новую функцию для элегантного решения конкатенации многострочных строк без глупости FOR XML PATH.

saber tabatabaee yazdi
27 декабря 2014 в 02:10
0

пошаговое руководство для описанных выше ответов: попробуйте эту статью: [sqlmatters.com/Articles/…]

Stack Man
27 мая 2015 в 07:56
4

Не SQL, но если это одноразовая вещь, вы можете вставить список в этот инструмент в браузере convert.town/column-to-comma-separated-list

Richard
6 июля 2017 в 06:32
3

В Oracle вы можете использовать LISTAGG (COLUMN_NAME) из 11g r2, до этого существует неподдерживаемая функция WM_CONCAT (COLUMN_NAME), которая делает то же самое.

Biju jose
20 мая 2018 в 08:22
0

это решение CLR, которое можно подключить напрямую, похоже на мой sql GROUP_CONCAT, здесь

JohnyL
2 августа 2018 в 18:36
0

Вы можете использовать функцию конкатенации строк. Я не могу добавить ответ (так как он заблокирован), поэтому добавляю ответ здесь: DECLARE @big_string varchar(max) = ''; SELECT @big_string += x.s + ',' FROM (VALUES ('string1'), ('string2'), ('string3')) AS x(s);. Теперь покажем результат: SELECT @big_string;. Это так просто.

ترمیناتور
3 августа 2021 в 15:31
0

ВЫБРАТЬ Main.SubjectID, LEFT (Main.Students, Len (Main.Students) -1) как «Студенты» ОТ (ВЫБРАТЬ DISTINCT ST2.SubjectID, (SELECT ST1.StudentName + ',' AS [text ()] FROM dbo. Студенты ST1 ГДЕ ST1.SubjectID = ST2.SubjectID ЗАКАЗАТЬ ST1.SubjectID ДЛЯ ПУТИ XML ('')) [Студенты] ИЗ dbo.Students ST2) [Main]

Ответы (48)

avatar
Ritesh
13 февраля 2009 в 11:53
1547

Если вы используете 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

Menefee
21 марта 2012 в 18:21
1

Я получаю сообщение об ошибке «Неправильный синтаксис рядом с ключевым словом« Для »» при работе с MS SQL Server 2008 R2

user140628
17 апреля 2013 в 12:35
16

Отличное решение. Следующее может быть полезно, если вам нужно обрабатывать специальные символы, подобные тем, что есть в HTML: Роб Фарли: Обработка специальных символов с помощью FOR XML PATH ('').

JsonStatham
26 июля 2013 в 14:37
0

Что делать, если нет «ID темы»

Sam
13 августа 2013 в 01:26
11

По-видимому, это не работает, если имена содержат символы XML, такие как < или &. См. Комментарий @ BenHinman.

James L.
23 мая 2014 в 05:19
0

Это хорошее решение. Я соединил с ним 20000 GUID менее чем за 1 секунду. for xml path('') работает намного лучше, чем любой подход с типом cursor и / или переменным concat.

Bacon Bits
13 ноября 2014 в 18:54
25

NB: этот метод основан на недокументированном поведении FOR XML PATH (''). Это означает, что его нельзя считать надежным, поскольку любой патч или обновление может изменить его работу. Он в основном полагается на устаревшую функцию.

Whelkaholism
23 марта 2015 в 10:54
0

@Bacon Bits - Не могли бы вы объяснить это поподробнее? Это невероятно широко используемый фрагмент кода.

Bacon Bits
23 марта 2015 в 14:00
1

@Whelkaholism Это был первый ответ Microsoft на этот запрос. Кажется, я больше не могу найти их исходное утверждение (как вы сказали, оно очень широко используется), но в течение многих лет после Server 2005 команда SQL Server утверждала, что FOR XML PATH ('') в сочетании с безымянными столбцами имеет неопределенное поведение (т. Е. Поведение не входит в спецификацию дизайна). Даже в 2014 году вы не увидите FOR XML PATH (''), используемого с безымянными столбцами в документации SQL Server. FOR XML PATH с безымянными столбцами, да. FOR XML PATH ('') с именованными столбцами, да. Но это дает разные результаты.

Bacon Bits
23 марта 2015 в 14:15
29

@Whelkaholism Суть в том, что FOR XML предназначен для генерации XML, а не для объединения произвольных строк. Вот почему он экранирует &, < и > в коды объектов XML (&amp;, &lt;, &gt;). Я предполагаю, что он также ускользнет от " и ' до &quot; и &apos; в атрибутах. Это , а не GROUP_CONCAT(), string_agg(), array_agg(), listagg() и т. Д., Даже если вы можете заставить его это сделать. Мы должны тратить время на то, чтобы требовать от Microsoft реализации надлежащей функции.

paio
11 августа 2015 в 13:24
0

Я должен использовать AS 'data ()' вместо AS [text ()].

Riley Major
1 октября 2015 в 22:00
1

@BaconBits В этом ответе используется улучшенный синтаксис, который лучше справляется со специальными символами за счет использования ключевого слова TYPE и извлечения содержимого с помощью функции XML value. Используя этот метод, вы даже можете использовать имена тегов и создавать правильно сформированный XML. Но не стоит бояться колонок без названий. Они официально поддерживаются как минимум еще в SQL Server 2008.

Graeme
1 июня 2016 в 20:39
1

Чтобы удалить начальную запятую, я обычно использую ROW_NUMBER (). Полный образец в ответе ниже. CASE ROW_NUMBER () OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ',' END + stu.Name

Jason C
6 апреля 2017 в 00:32
17

Хорошие новости: MS SQL Server добавит string_agg в v.Next. и все это может уйти.

rrozema
13 ноября 2017 в 15:29
0

Добавлен улучшенный пример решения проблемы, на которую указывает @Sam, символы <,> и &, возвращаемые как escape-последовательности xml & lt ;, & gt; и & amp ;.

Jamie Kitson
27 ноября 2017 в 11:12
0

Вы можете использовать STUFF вместо SUBSTRING, чтобы удалить начальную запятую.

jpaugh
19 марта 2018 в 14:13
0

Это очень плохой ответ! Он не дает простого, работоспособного примера и не объясняет, какие части необходимы для его работы. Может ли кто-нибудь, кто это понимает, сделать это лучше? Может ты, @ P5Coder?

Ganesh Jadhav
19 марта 2018 в 18:49
0

@jpaugh почему я?

jpaugh
19 марта 2018 в 20:41
0

Я выбрал вас, потому что вы редактировали его раньше и, вероятно, понимаете это. Однако с тех пор я нашел ответ Макса на эту роль, и оказалось, что он использовал тот же трюк SQL, хотя сначала я не узнал его из-за всего шума.

Maksym Sadovnychyy
29 июня 2018 в 09:39
0

вы можете добавить в свой пример и эту часть. Выберите Main.SubjectID, CASE WHEN Main.Students Like '%,%' THEN Left (Main.Students, Len (Main.Students) -1) ELSE Main.Students END AS [Студенты]

Salman A
26 января 2019 в 18:49
0

Вам нужно использовать GROUP BY вместо DISTINCT, это должно дать вам идентичные результаты с возможно более высокой производительностью.

andynaz
1 февраля 2019 в 12:00
1

используя AS [text()], сделайте поле окруженным тегом <text> ... Я просто удаляю его, и все работает (SQL Server 2008)

avatar
sameer Ahmed
4 апреля 2021 в 17:34
12

В ms sql Server 2017 или более поздних версиях вы можете использовать функцию STRING_AGG () для генерации разделенных запятыми значений <509899 one8876762>. .

SELECT
VendorId,STRING_AGG(FirstName,',') UsersName FROM
Users
where VendorId!=9 GROUP BY VendorId

enter image description here

avatar
panser
14 января 2021 в 09:08
2

в Postgres - array_agg

SELECT array_to_string(array_agg(DISTINCT rolname), ',') FROM pg_catalog.pg_roles;

ИЛИ STRING_AGG

SELECT STRING_AGG(rolname::text,',') FROM pg_catalog.pg_roles;
avatar
Amirreza mohammadi
13 ноября 2020 в 11:33
0

Прежде всего вы должны объявить табличную переменную и заполнить ее данными таблицы, а затем с помощью цикла WHILE выбрать строку одну за другой и добавить ее значение в переменную nvarchar (max).

    Go
    declare @temp table(
        title nvarchar(50)
    )
    insert into @temp(title)
    select p.Title from dbo.person p
    --
    declare @mainString nvarchar(max)
    set @mainString = '';
    --
    while ((select count(*) from @temp) != 0)
    begin
        declare @itemTitle nvarchar(50)
        set @itemTitle = (select top(1) t.Title from @temp t)
    
        if @mainString = ''
        begin
            set @mainString = @itemTitle
        end
        else
        begin
            set @mainString = concat(@mainString,',',@itemTitle)
        end
    
        delete top(1) from @temp
    
    end
    print @mainString
MeanGreen
13 ноября 2020 в 12:07
1

Похоже, хороший ответ, но объясните, пожалуйста, как работает этот код.

user2864740
30 января 2021 в 19:51
0

Похоже, что у него были бы очень неэффективные границы - как это будет работать с миллионом строк?

avatar
Arash.Zandi
16 сентября 2019 в 12:21
13

Это сработало для меня ( SqlServer 2016 ):

SELECT CarNamesString = STUFF((
         SELECT ',' + [Name]
            FROM tbl_cars 
            FOR XML PATH('')
         ), 1, 1, '')

Вот источник: https://www.mytecbits.com/

И решение для MySql (поскольку эта страница отображается в Google для MySql)

SELECT [Name],
       GROUP_CONCAT(DISTINCT [Name]  SEPARATOR ',')
       FROM tbl_cars

Из Документация MySql

avatar
asmgx
16 августа 2019 в 00:20
5

поверх ответа Криса Шаффера

, если ваши данные могут повторяться, например,

Tom
Ali
John
Ali
Tom
Mike

Вместо Tom,Ali,John,Ali,Tom,Mike

Вы можете использовать DISTINCT, чтобы избежать дублирования и получить Tom,Ali,John,Mike

DECLARE @Names VARCHAR(8000) 
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names
avatar
Kemal AL GAZZAH
23 апреля 2019 в 17:42
0

Мы можем использовать RECUSRSIVITY, WITH CTE, union ALL следующим образом

declare @mytable as table(id int identity(1,1), str nvarchar(100))
insert into @mytable values('Peter'),('Paul'),('Mary')

declare @myresult as table(id int,str nvarchar(max),ind int, R# int)

;with cte as(select id,cast(str as nvarchar(100)) as str, cast(0 as int) ind from @mytable
union all
select t2.id,cast(t1.str+',' +t2.str as nvarchar(100)) ,t1.ind+1 from cte t1 inner join @mytable t2 on t2.id=t1.id+1)
insert into @myresult select *,row_number() over(order by ind) R# from cte

select top 1 str from @myresult order by R# desc
avatar
Esperento57
31 июля 2018 в 06:13
1

Вы можете сделать это с помощью рекурсивного запроса:

-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;

-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');

-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank  where rang=1
union all
select f0.*,  cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName 
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1 
)
select AllName from tmpRecursive
where rang=NbRow
avatar
Steven Chong
23 июля 2018 в 16:55
326

В 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   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

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

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

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


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

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

David
11 августа 2011 в 23:12
3

Хорошее использование функции STUFF для удаления первых двух символов.

R. Schreurs
2 августа 2013 в 08:27
3

Мне больше всего нравится это решение, потому что я могу легко использовать его в списке выбора, добавив 'as <label>'. Я не уверен, как это сделать с помощью решения @Ritesh.

BateTech
7 апреля 2014 в 21:35
14

Это лучше, чем принятый ответ, потому что этот параметр также обрабатывает неэкранированные зарезервированные символы XML, такие как <, >, & и т. Д., Которые FOR XML PATH('') будут автоматически экранированы.

avatar
Aura
2 апреля 2018 в 14:40
1

@ User1460901 Вы можете попробовать что-то вроде этого:

WITH cte_base AS (
    SELECT CustomerCode, CustomerName,
    CASE WHEN Typez = 'Breakfast' THEN Items ELSE NULL END AS 'BREAKFAST'
    , CASE WHEN Typez = 'Lunch' THEN Items ELSE NULL END AS 'LUNCH'
    FROM #Customer
    )
    SELECT distinct CustomerCode, CustomerName,
    SUBSTRING(
    (   
        SELECT ','+BREAKFAST AS [text()]
        FROM cte_base b1
        WHERE b1.CustomerCode = b2.CustomerCode AND b1.CustomerName = b2.CustomerName
        ORDER BY b1.BREAKFAST
        FOR XML PATH('')
        ), 2, 1000
    ) [BREAKFAST], 
    SUBSTRING(
    (   
        SELECT ','+LUNCH AS [text()]
        FROM cte_base b1
        WHERE b1.CustomerCode = b2.CustomerCode AND b1.CustomerName = b2.CustomerName
        ORDER BY b1.LUNCH
        FOR XML PATH('')
        ), 2, 1000
    ) [LUNCH]
    FROM cte_base b2
avatar
Ravi Pipaliya
2 апреля 2018 в 13:04
3

Вот полное решение для достижения этой цели:

-- Table Creation
CREATE TABLE Tbl
( CustomerCode    VARCHAR(50)
, CustomerName    VARCHAR(50)
, Type VARCHAR(50)
,Items    VARCHAR(50)
)

insert into Tbl
SELECT 'C0001','Thomas','BREAKFAST','Milk'
union SELECT 'C0001','Thomas','BREAKFAST','Bread'
union SELECT 'C0001','Thomas','BREAKFAST','Egg'
union SELECT 'C0001','Thomas','LUNCH','Rice'
union SELECT 'C0001','Thomas','LUNCH','Fish Curry'
union SELECT 'C0001','Thomas','LUNCH','Lessy'
union SELECT 'C0002','JOSEPH','BREAKFAST','Bread'
union SELECT 'C0002','JOSEPH','BREAKFAST','Jam'
union SELECT 'C0002','JOSEPH','BREAKFAST','Tea'
union SELECT 'C0002','JOSEPH','Supper','Tea'
union SELECT 'C0002','JOSEPH','Brunch','Roti'

-- function creation
GO
CREATE  FUNCTION [dbo].[fn_GetItemsByType]
(   
    @CustomerCode VARCHAR(50)
    ,@Type VARCHAR(50)
)
RETURNS @ItemType TABLE  ( Items VARCHAR(5000) )
AS
BEGIN

        INSERT INTO @ItemType(Items)
    SELECT  STUFF((SELECT distinct ',' + [Items]
         FROM Tbl 
         WHERE CustomerCode = @CustomerCode
            AND Type=@Type
            FOR XML PATH(''))
        ,1,1,'') as  Items



    RETURN 
END

GO

-- fianl Query
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type) 
                    from Tbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT CustomerCode,CustomerName,' + @cols + '
             from 
             (
                select  
                    distinct CustomerCode
                    ,CustomerName
                    ,Type
                    ,F.Items
                    FROM Tbl T
                    CROSS APPLY [fn_GetItemsByType] (T.CustomerCode,T.Type) F
            ) x
            pivot 
            (
                max(Items)
                for Type in (' + @cols + ')
            ) p '

execute(@query) 
avatar
Pooja Bhat
15 февраля 2018 в 09:35
2

Ниже представлена ​​простая процедура PL / SQL для реализации данного сценария с использованием «базового цикла» и «rownum»

Определение таблицы

CREATE TABLE "NAMES" ("NAME" VARCHAR2(10 BYTE))) ;

Давайте вставим значения в эту таблицу

INSERT INTO NAMES VALUES('PETER');
INSERT INTO NAMES VALUES('PAUL');
INSERT INTO NAMES VALUES('MARY');

Отсюда начинается процедура

DECLARE 

MAXNUM INTEGER;
CNTR INTEGER := 1;
C_NAME NAMES.NAME%TYPE;
NSTR VARCHAR2(50);

BEGIN

SELECT MAX(ROWNUM) INTO MAXNUM FROM NAMES;

LOOP

SELECT NAME INTO  C_NAME FROM 
(SELECT ROWNUM RW, NAME FROM NAMES ) P WHERE P.RW = CNTR;

NSTR := NSTR ||','||C_NAME;
CNTR := CNTR + 1;
EXIT WHEN CNTR > MAXNUM;

END LOOP;

dbms_output.put_line(SUBSTR(NSTR,2));

END;

Результат

PETER,PAUL,MARY
jpaugh
19 марта 2018 в 13:58
0

Вопрос требует ответа, специфичного для SQL Server. Если есть вопрос PL / SQL, вы можете вместо этого ответить там. Однако сначала проверьте wm_concat и посмотрите, является ли это более простым методом.

avatar
Max Szczurek
25 января 2018 в 04:55
15
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')

Вот пример:

DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary
avatar
Shahbaz
7 декабря 2017 в 17:20
2

Хотя уже поздно, и решений уже много. Вот простое решение для MySQL:

SELECT t1.id,
        GROUP_CONCAT(t1.id) ids
 FROM table t1 JOIN table t2 ON (t1.id = t2.id)
 GROUP BY t1.id
jpaugh
19 марта 2018 в 13:59
0

Этот вопрос специфичен для SQL-сервера, поэтому вряд ли тот, кому он нужен. Есть ли вопрос об этом же, связанный с mysql?

avatar
Oleg Sakharov
23 мая 2017 в 11:55
7

Мне очень понравилась элегантность ответа Даны. Просто хотел закончить.

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

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

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
JT_
18 декабря 2015 в 11:04
0

Если вы удаляете последние два символа ',', вам нужно добавить ',' после имени ('SELECT \ @names = \ @names + Name +', 'FROM Names'). Таким образом, последние два символа всегда будут ','.

Tian van Heerden
4 марта 2016 в 09:13
0

В моем случае мне нужно было избавиться от ведущей запятой , поэтому измените запрос на SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ', ' END + Name FROM Names, тогда вам не нужно будет усекать его впоследствии.

avatar
Mathieu Renda
14 марта 2017 в 05:00
621

SQL Server 2017+ и SQL Azure: STRING_AGG

Начиная со следующей версии SQL Server, мы можем, наконец, объединить строки, не прибегая к какой-либо переменной или XML-колдовству.

STRING_AGG (Transact-SQL)

Без группировки

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;
canon
10 июля 2017 в 16:17
2

И, в отличие от решений CLR, вы можете контролировать сортировку.

InspiredBy
3 марта 2020 в 05:04
0

Похоже, на STRING_AGG есть ограничение на отображение 4000 символов.

RuudvK
10 мая 2020 в 09:01
1

Есть ли способ выполнить сортировку в случае отсутствия GROUP BY (например, для примера «Без группировки»)?

RuudvK
10 мая 2020 в 09:11
0

Обновление: мне удалось сделать следующее, но есть ли более чистый способ? ВЫБЕРИТЕ STRING_AGG (Имя, ',') КАК ОТДЕЛЫ ИЗ (ВЫБРАТЬ ВЕРХНИЕ 100000 Имя ИЗ HumanResources. Отделение ORDER BY Name) D;

Varun
26 сентября 2020 в 03:25
0

Мне пришлось применить его к NVarchar (max), чтобы он заработал .. `` SELECT STRING_AGG (CAST (EmpName as NVARCHAR (MAX)), ',') FROM EmpTable as t '' '

avatar
Henrik Fransas
21 ноября 2016 в 11:27
22

В SQL Server vNext это будет встроено с функцией STRING_AGG, подробнее об этом читайте здесь: https://msdn.microsoft.com/en-us/library/mt790580.aspx

avatar
Tigerjz32
15 ноября 2016 в 21:07
26

Вам нужно создать переменную, которая будет содержать ваш окончательный результат, и выбрать его, например, так.

Самое простое решение

DECLARE @char VARCHAR(MAX);

SELECT @char = COALESCE(@char + ', ' + [column], [column]) 
FROM [table];

PRINT @char;
avatar
Glen
10 июня 2016 в 02:03
3

Не то, чтобы я проводил какой-либо анализ производительности, поскольку в моем списке было менее 10 пунктов, но я был поражен, просмотрев 30 с лишним ответов. У меня все еще был поворот в аналогичном ответе, который уже был дан, аналогично использованию COALESCE для одной группы list, и мне даже не нужно было устанавливать мою переменную (по умолчанию все равно NULL), и он предполагает, что все записи в моей таблице исходных данных не пустые:

DECLARE @MyList VARCHAR(1000), @Delimiter CHAR(2) = ', '
SELECT @MyList = CASE WHEN @MyList > '' THEN @MyList + @Delimiter ELSE '' END + FieldToConcatenate FROM MyData

Я уверен, что внутри COALESCE используется та же идея. Будем надеяться, что MS не изменит это на мне.

avatar
Graeme
1 июня 2016 в 20:42
4

--SQL Server 2005+

CREATE TABLE dbo.Students
(
    StudentId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

CREATE TABLE dbo.Subjects
(
    SubjectId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);

CREATE TABLE dbo.Schedules
(
    StudentId INT
    , SubjectId INT
    , CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
    , CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
    , CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);

INSERT dbo.Students (StudentId, Name) VALUES
    (1, 'Mary')
    , (2, 'John')
    , (3, 'Sam')
    , (4, 'Alaina')
    , (5, 'Edward')
;

INSERT dbo.Subjects (SubjectId, Name) VALUES
    (1, 'Physics')
    , (2, 'Geography')
    , (3, 'French')
    , (4, 'Gymnastics')
;

INSERT dbo.Schedules (StudentId, SubjectId) VALUES
    (1, 1)      --Mary, Physics
    , (2, 1)    --John, Physics
    , (3, 1)    --Sam, Physics
    , (4, 2)    --Alaina, Geography
    , (5, 2)    --Edward, Geography
;

SELECT 
    sub.SubjectId
    , sub.Name AS [SubjectName]
    , ISNULL( x.Students, '') AS Students
FROM
    dbo.Subjects sub
    OUTER APPLY
    (
        SELECT 
            CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
            + stu.Name
        FROM
            dbo.Students stu
            INNER JOIN dbo.Schedules sch
                ON stu.StudentId = sch.StudentId
        WHERE
            sch.SubjectId = sub.SubjectId
        ORDER BY
            stu.Name
        FOR XML PATH('')
    ) x (Students)
;
avatar
Muhammad Bilal
26 мая 2016 в 11:14
2
SELECT PageContent = Stuff(
    (   SELECT PageContent
        FROM dbo.InfoGuide
        WHERE CategoryId = @CategoryId
          AND SubCategoryId = @SubCategoryId
        for xml path(''), type
    ).value('.[1]','nvarchar(max)'),
    1, 1, '')
FROM dbo.InfoGuide info
avatar
Mike Barlow - BarDev
4 мая 2016 в 19:31
12

Что касается других ответов, человек, читающий ответ, должен знать определенную таблицу доменов, например, автомобиль или студент. Чтобы проверить решение, необходимо создать таблицу и заполнить ее данными.

Ниже приведен пример, в котором используется таблица SQL Server «Information_Schema.Columns». При использовании этого решения не нужно создавать таблицы или добавлять данные. В этом примере создается список имен столбцов, разделенных запятыми, для всех таблиц в базе данных.

SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME 
avatar
pedram
5 апреля 2016 в 07:08
61

Используйте COALESCE - Подробнее здесь

Например:

102

103

104

Затем напишите ниже код на сервере sql,

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
EvilDr
3 августа 2016 в 15:01
2

Это действительно лучшее решение IMO, поскольку оно позволяет избежать проблем с кодировкой, которые представляет FOR XML. Я использовал Declare @Numbers AS Nvarchar(MAX), и он работал нормально. Не могли бы вы объяснить, почему вы не рекомендуете его использовать?

Andre Figueiredo
3 мая 2017 в 21:53
9

Это решение было опубликовано 8 лет назад! coderhelper.com/a/194887/986862

Akmal Salikhov
7 декабря 2017 в 11:31
0

Почему этот запрос возвращается ??? символы вместо кириллических? Это просто проблема с выводом?

Developer Webs
10 марта 2021 в 19:40
0

@EvilDr Вы можете избежать кодирования XML. См .: coderhelper.com/questions/15643683/…

avatar
user1767754
22 июля 2015 в 07:51
7

Пример завершения MySQL:

У нас есть пользователи, у которых может быть много данных, и мы хотим получить вывод, в котором мы можем видеть все данные пользователей в списке:

Результат :

___________________________
| id   |  rowList         |
|-------------------------|
| 0    | 6, 9             |
| 1    | 1,2,3,4,5,7,8,1  |
|_________________________|

Настройка таблицы:

CREATE TABLE `Data` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);


CREATE TABLE `User` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `User` (`id`) VALUES
(0),
(1);

Запрос:

SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id
avatar
Nizam
8 мая 2015 в 01:39
6

Этот ответ потребует некоторых привилегий на сервере для работы.

Сборки - хороший вариант для вас. Есть много сайтов, которые объясняют, как его создать. Я думаю, что это очень хорошо объяснено: это one

Если хотите, я уже создал сборку, и можно скачать DLL здесь.

После того, как вы его загрузили, вам нужно будет запустить следующий сценарий на вашем SQL Server:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

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

Надеюсь, это поможет !!!


ОБНОВЛЕНИЕ :

Начиная с MS-SQL 2017, можно использовать функцию STRING_AGG, функцию

Protiguous
19 февраля 2020 в 13:37
1

Ссылка DLL содержит ошибку 404. Использование сборки для этого - излишество. См. Лучший ответ для SQL Server.

avatar
Hamid Bahmanabady
20 апреля 2015 в 06:07
-5
   declare @phone varchar(max)='' 
   select @phone=@phone + mobileno +',' from  members
   select @phone
shA.t
20 апреля 2015 в 07:05
0

Почему бы и нет +', ' Как хотел OP, а также вы не удаляете последний ';'. Я думаю, что этот ответ такой же, как и этот ответ;).

Hamid Bahmanabady
20 апреля 2015 в 13:24
0

У меня была эта проблема, и я нашел ответ, но я хочу Concatenate с ';' поэтому я вставляю его сюда, последний элемент пуст

shA.t
20 апреля 2015 в 13:43
3

Когда вы публикуете здесь свой ответ, он должен быть связан с вопросом. И результат вашего кода должен быть Null, потому что вы начинаете с @phone IS Null, а добавление к Null будет Null в SQL Server, я думаю, вы забыл что-то вроде добавления = '' после вашей первой строки;).

Hamid Bahmanabady
20 апреля 2015 в 17:36
0

Нет, я отправляю ответ после проверки, и результат не был нулевым

avatar
Rapunzo
12 февраля 2015 в 12:01
7

Чтобы избежать нулевых значений, вы можете использовать CONCAT ()

DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name) 
FROM Names
select @names
Reversed Engineer
20 сентября 2016 в 08:15
0

Было бы неплохо узнать , почему работает CONCAT. Ссылка на MSDN была бы хороша.

avatar
Max Tkachenko
2 декабря 2013 в 09:25
1

С типом ТАБЛИЦА это очень просто. Представьте, что ваша таблица называется Students и имеет столбец name.

declare @rowsCount INT
declare @i INT = 1
declare @names varchar(max) = ''

DECLARE @MyTable TABLE
(
  Id int identity,
  Name varchar(500)
)
insert into @MyTable select name from Students
set @rowsCount = (select COUNT(Id) from @MyTable)

while @i < @rowsCount
begin
 set @names = @names + ', ' + (select name from @MyTable where Id = @i)
 set @i = @i + 1
end
select @names

Этот пример протестирован в MS SQL Server 2008 R2

avatar
topchef
15 ноября 2013 в 20:26
3

Этот метод применим только к базе данных Teradata Aster, поскольку она использует свою функцию NPATH.

Опять же, у нас есть таблица "Студенты"

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

Тогда с NPATH это просто один SELECT:

SELECT * FROM npath(
  ON Students
  PARTITION BY SubjectID
  ORDER BY StudentName
  MODE(nonoverlapping)
  PATTERN('A*')
  SYMBOLS(
    'true' as A
  )
  RESULT(
    FIRST(SubjectID of A) as SubjectID,
    ACCUMULATE(StudentName of A) as StudentName
  )
);

Результат:

SubjectID       StudentName
----------      -------------
1               [John, Mary, Sam]
2               [Alaina, Edward]
avatar
endo64
25 октября 2013 в 08:14
4

Это тоже может быть полезно

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
blueling
5 декабря 2013 в 09:11
5

К сожалению, это поведение официально не поддерживается. MSDN говорит: «Если на переменную есть ссылка в списке выбора, ей должно быть присвоено скалярное значение, или оператор SELECT должен возвращать только одну строку». И есть люди, которые наблюдали проблемы: sqlmag.com/sql-server/multi-row-variable-assignment-and-order

avatar
ZeroK
13 мая 2013 в 16:02
7

Для баз данных Oracle см. Этот вопрос: Как можно объединить несколько строк в одну в Oracle без создания хранимой процедуры?

Наилучшим ответом, по-видимому, является @Emmanuel, использующий встроенную функцию LISTAGG (), доступную в Oracle 11g Release 2 и более поздних версиях.

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 функция WM_CONCAT () тоже вариант. Он кажется стабильным, но Oracle явно не рекомендует использовать его для SQL любого приложения, поэтому используйте его на свой страх и риск.

Кроме этого, вам придется написать свою собственную функцию; в документе Oracle выше есть руководство о том, как это сделать.

avatar
Priti Getkewar Joshi
28 сентября 2012 в 20:44
2

В oracle есть несколько способов,

    create table name
    (first_name varchar2(30));

    insert into name values ('Peter');
    insert into name values ('Paul');
    insert into name values ('Mary');

Решение 1:

    select substr(max(sys_connect_by_path (first_name, ',')),2) from (select rownum r, first_name from name ) n start with r=1 connect by prior r+1=r
    o/p=> Peter,Paul,Mary

Ситуация 2:

    select  rtrim(xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') first_name from name
    o/p=> Peter,Paul,Mary
avatar
hgmnz
9 августа 2012 в 21:20
51

Массивы 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)

ВЫПОЛНЕНО

Начиная с PostgreSQL 9.0 это стало еще проще.

ProbablePrime
27 февраля 2015 в 11:50
0

Если вам нужно более одного столбца, например, их идентификатор сотрудника в скобках, используйте оператор concat: select array_to_string(array_agg(name||'('||id||')'

GoldBishop
4 мая 2017 в 15:03
0

Не применимо к sql-server, только к mysql

avatar
jmoreno
9 августа 2012 в 21:06
31

Было предложено рекурсивное решение 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
knb
24 июля 2017 в 13:34
2

Для ошеломленных: этот запрос вставляет 12 строк (3 столбца) во временную базовую таблицу, затем создает рекурсивное общее табличное выражение (rCTE), а затем сглаживает столбец name в строку с разделителями-запятыми для 4 групп из id с. На первый взгляд, мне кажется, что это больше работы, чем то, что делают большинство других решений для SQL Server.

jmoreno
25 июля 2017 в 02:20
3

@knb: не уверен, это похвала, осуждение или просто сюрприз. Базовая таблица создана, потому что мне нравится, что мои примеры действительно работают, она не имеет ничего общего с вопросом.

avatar
Alex
8 марта 2012 в 16:29
47

Oracle 11g Release 2 поддерживает функцию LISTAGG. Документация здесь.

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 символов.

toscanelli
20 июля 2015 в 13:04
1

Для более старых версий Oracle идеально подходит wm_concat. Его использование объясняется в подарке по ссылке Алексом. Спасибо, Алекс!

asgs
22 июня 2016 в 18:56
0

LISTAGG работает отлично! Просто прочтите документ, ссылка на который есть здесь. wm_concat удалено из версии 12c и новее.

avatar
Daniel Reis
3 февраля 2012 в 10:39
18

Готовое решение без лишних запятых:

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

Пустой список приведет к значению NULL. Обычно вы вставляете список в столбец таблицы или программную переменную: отрегулируйте максимальную длину 255 в соответствии с вашими потребностями.

(Дивакар и Йенс Франдсен дали хорошие ответы, но нуждаются в улучшении.)

slayernoah
18 ноября 2015 в 18:23
0

При использовании этого слова перед запятой ставится пробел :(

Daniel Reis
18 ноября 2015 в 23:17
1

Просто замените ', ' на ',', если вам не нужно дополнительное пространство.

avatar
Pramod
27 июля 2011 в 20:05
5

Если вы хотите иметь дело с значениями NULL, вы можете сделать это, добавив предложение where или добавив еще один COALESCE вокруг первого.

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People
avatar
Yogesh Bhadauirya
6 июля 2011 в 12:46
35

В 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
Sam
13 августа 2013 в 01:36
2

Я считаю, что это не работает, если значения содержат символы XML, такие как < или &.

avatar
Vladimir Nesterovsky
6 июля 2011 в 06:58
5

Я обычно использую такой выбор для объединения строк в 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
avatar
user762952
3 июня 2011 в 18:14
4

В Oracle это wm_concat. Я считаю, что эта функция доступна в версии 10g и выше.

avatar
Hans Bluh
29 апреля 2011 в 15:28
1

Как насчет этого:

   ISNULL(SUBSTRING(REPLACE((select ',' FName as 'data()' from NameList for xml path('')), ' ,',', '), 2, 300), '') 'MyList'

Где "300" может быть любой шириной с учетом максимального количества элементов, которые, по вашему мнению, будут отображаться.

Geoff Griswald
10 января 2020 в 10:57
0

Если вы когда-нибудь обнаружите, что заранее угадываете, сколько строк будет в ваших результатах, вы делаете это неправильно.

avatar
Diwakar
7 апреля 2011 в 11:16
18

Использование XML помогло мне разделить строки запятыми. Для дополнительной запятой мы можем использовать функцию замены SQL Server. Вместо того, чтобы добавлять запятую, использование AS 'data ()' объединит строки с пробелами, которые позже можно будет заменить запятыми в соответствии с синтаксисом, описанным ниже.

REPLACE(
        (select FName AS 'data()'  from NameList  for xml path(''))
         , ' ', ', ') 
David Roussel
2 июня 2011 в 16:22
2

На мой взгляд, это лучший ответ. Использование объявить переменную не подходит, когда вам нужно присоединиться к другой таблице, и это красиво и коротко. Хорошая работа.

binball
8 июня 2011 в 15:16
8

это не работает, если в данных FName уже есть пробелы, например «Мое имя»

Rejwanul Reja
28 апреля 2017 в 10:13
0

На самом деле он работает для меня на ms-sql 2016 Выберите REPLACE ((выберите Name AS 'data ()' from Brand Where Id IN (1,2,3,4) для пути xml ('')), '', ' , ') как allBrands

avatar
jens frandsen
5 апреля 2011 в 21:19
380

Один метод, еще не показанный с помощью команды 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
Ben
7 сентября 2012 в 15:56
16

святое дерьмо, это потрясающе! При выполнении самостоятельно, как в вашем примере, результат форматируется как гиперссылка, при нажатии на которую (в SSMS) открывается новое окно, содержащее данные, но при использовании как части более крупного запроса оно просто отображается как строка. Это строка? или это xml, к которому мне нужно относиться по-другому в приложении, которое будет использовать эти данные?

Lukáš Lánský
26 февраля 2014 в 18:34
10

Этот подход также экранирует символы XML, такие как <и>. Итак, ВЫБОР '<b>' + FName + '</b>' приведет к "& lt; b & gt; John & lt; / b & gt; & lt; b & gt; Paul ..."

Baodad
3 октября 2014 в 22:40
8

Аккуратное решение. Я заметил, что даже когда я не добавляю + ', ', он все равно добавляет один пробел между каждым объединенным элементом.

slayernoah
18 ноября 2015 в 01:22
0

Аккуратный! Есть идеи, как бороться с запятой в конце?

NReilingh
29 февраля 2016 в 18:12
8

@Baodad Похоже, это часть сделки. Вы можете обойти эту проблему, заменив добавленный символ токена. Например, это идеальный список с разделителями-запятыми любой длины: SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'')

NReilingh
29 февраля 2016 в 18:28
0

Ха-ха, но в этот момент вы можете просто не использовать data () в первую очередь, а просто выполнить один из приведенных выше примеров. Кажется, что data() на самом деле всего лишь сокращение от «разграничить это пробелом», и если вам нужно что-то еще, это бесполезно - если только это не сказывается на производительности.

NReilingh
29 февраля 2016 в 18:33
1

Ничего себе, на самом деле в моем тестировании с использованием data () и замены ПУТЬ более производительно, чем нет. Очень странно.

illmortem
27 июля 2017 в 21:00
0

Если вы замените data () на text (), он, похоже, сгенерирует список без необходимости обрезать пробелы.

LoztInSpace
17 февраля 2020 в 22:46
0

Хорошо - спасибо. Чтобы удалить запятую, я просто ставлю запятую впереди, а затем беру SUBSTRING( (SELECT ','+FName AS 'data()' FROM NameList FOR XML PATH('')),2,100000), чтобы пропустить начальную.

Spik330
17 июня 2020 в 17:03
0

Как бы вы это сгруппировали? скажем, у меня есть две таблицы people и roles, у человека может быть много ролей. Я хочу получить строку с разделителями-запятыми для каждой роли человека.

avatar
gbn
13 октября 2008 в 17:24
10
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

Это помещает случайную запятую в начало.

Однако, если вам нужны другие столбцы или дочерняя таблица в CSV, вам необходимо заключить это в скалярное определяемое пользователем поле (UDF).

Вы также можете использовать путь XML в качестве коррелированного подзапроса в предложении SELECT (но мне придется подождать, пока я вернусь к работе, потому что Google не выполняет работу дома :-)

avatar
Chris Shaffer
12 октября 2008 в 00:18
1070

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

Используйте COALESCE:

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

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

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

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

2) Нет необходимости снимать лишний разделитель на конце.

  • Приведенное выше решение даст неверные результаты, если строка имеет значение NULL Имя (если имеется NULL , NULL <175385695697702> приведет к 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' на то, что вам подходит ]).

Graeme Perrow
13 февраля 2009 в 12:02
76

Чтобы было ясно, coalesce не имеет ничего общего с созданием списка, он просто гарантирует, что значения NULL не включены.

user166390
15 августа 2010 в 18:57
19

@Graeme Perrow Он не исключает значения NULL (для этого требуется WHERE - это приведет к потере результатов , если одно из входных значений NULL), и в этом подходе требуется потому что: NULL + не NULL -> NULL и не NULL + NULL -> NULL; также @Name по умолчанию имеет значение NULL, и, фактически, это свойство используется здесь как неявный дозорный, чтобы определить, следует ли добавлять ',' или нет.

krubo
20 июня 2011 в 01:14
1

Два способа исправить это, чтобы изящно игнорировать NULL: либо SELECT @Names = @Names + ', ' + Name FROM People WHERE Name IS NOT NULL, либо SELECT @Names = COALESCE(@Names + ', ' + Name, @Names) FROM People.

Kirk Broadhurst
25 августа 2011 в 04:34
5

@krubo Нет, проблема в том, что @Names = @Names + *anything* будет иметь значение NULL, потому что @Names имеет значение NULL при объявлении. COALESCE разрешает и нулевое значение Name, и , и начальное нулевое значение @Names.

XpiritO
28 октября 2011 в 14:06
1

Это не работает для типов данных varchar и ntext, поскольку они оба несовместимы с оператором добавления.

Chris Shaffer
28 октября 2011 в 17:11
2

@XpiritO - вы имеете в виду текст и ntext? Варчар совместим; текст и ntext могут быть преобразованы (если вы используете SQL 2005, преобразуйте их в VARCHAR (MAX) / NVARCHAR (MAX), и вы ничего не потеряете; в противном случае вам все равно придется принять возможность усечения, поскольку вы можете ' t объявить переменную text / ntext).

fbarber
26 апреля 2012 в 02:18
65

Обратите внимание, что этот метод конкатенации зависит от того, что SQL Server выполняет запрос с определенным планом. Меня поймали с использованием этого метода (с добавлением ORDER BY). Когда он имел дело с небольшим количеством строк, он работал нормально, но с большим количеством данных SQL Server выбрал другой план, в результате которого был выбран первый элемент без какой-либо конкатенации. См. эту статью Анит Сен.

R. Schreurs
2 августа 2013 в 08:10
17

Этот метод нельзя использовать в качестве подзапроса в списке выбора или в предложении where, поскольку он использует переменную tSQL. В таких случаях вы можете использовать методы, предлагаемые @Ritesh

Shinigamae
28 октября 2013 в 20:09
2

Это решение не будет работать в представлении, в отличие от решения Ритеша.

Iain Samuel McLean Elder
9 декабря 2013 в 00:33
1

Это самый простой способ создания динамического SQL, если вы хотите применить одну и ту же команду ко многим объектам. Cade Roux использует его для переименования схем, а я использую его для переименования тестовых классов tSQLt в. Спасибо, Крис!

James L.
23 мая 2014 в 05:16
2

Пробовал это, сначала понравилось, потому что это просто и блестяще. Но, как и любой другой повторяющийся процесс concat с varchar(), ввод-вывод и ЦП в конечном итоге привязываются. Для 20 000 GUID потребовалось 2 минуты, чтобы объединить их, тогда как использование for xml path('') заняло менее 1 секунды.

confusedMind
8 августа 2014 в 13:34
1

Как добавить к нему Distinct, если я хочу добавить только отдельные значения?

Shay
12 августа 2014 в 00:16
2

@confusedMind - вот так: DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM ( SELECT DISTINCT Name FROM People )

Marc Durdin
15 июля 2015 в 00:23
12

Это ненадежный метод объединения. Он не поддерживается и не должен использоваться (для Microsoft, например, support.microsoft.com/en-us/kb/287515, connect.microsoft.com/SQLServer/Feedback/Details/704389). Это может измениться без предупреждения. Используйте метод XML PATH, описанный в coderhelper.com/questions/5031204/… Подробнее здесь: marc.durdin.net/2015/07/…

Nezam
10 августа 2015 в 18:14
2

Основная проблема этого подхода заключается в том, что он усекает все значения, превышающие 8000.

binki
25 июня 2016 в 15:43
2

Ваше объяснение на самом деле не объясняет, что это делает. Он полагается на SQL Server, выполняющий выражение для каждой строки. Было бы здорово, если бы на это можно было положиться. Но см. Другие комментарии, указывающие на то, что SQL Server не требуется для этого типа запроса.

Leonardo Spina
30 августа 2016 в 11:21
1

Я нашел здесь тот же пример точного кода (опубликованный 6 месяцев назад): codeproject.com/Tips/334400/… Я думаю, что ссылка на автора должна быть обязательной.

Jon49
21 декабря 2016 в 20:40
2

Штопать. Я поддержал этот ответ, но теперь, когда я знаю, что это неправильный способ выполнения конкатенации, потому что он может вас обмануть, поскольку это неподдерживаемая функция, я больше не могу удалить свой голос :-( Теперь я ввел многих в заблуждение.

BI Dude
10 мая 2017 в 15:32
1

Решение работает нормально; однако он обрезает текст до длины 65576

MC9000
7 февраля 2019 в 01:19
1

Нельзя использовать ORDER BY для этого, не говоря уже о том, что это ужасный совет по причинам, опубликованным другими!

Ibrahim Inam
18 июня 2020 в 12:16
0

Спасибо за ваш вклад, это спасло мне день. Пожалуйста, дайте мне знать, сработает ли это, если строк больше сотни или больше? @ Мартин Смит

avatar
Dana
12 октября 2008 в 00:16
31

У меня нет доступа к SQL Server дома, поэтому я предполагаю здесь синтаксис, но он более или менее:

DECLARE @names VARCHAR(500)

SELECT @names = @names + ' ' + Name
FROM Names
Marc Gravell♦
12 октября 2008 в 09:10
12

Вам нужно будет инициализировать @names во что-то ненулевое, иначе вы получите NULL; вам также нужно будет обработать разделитель (включая ненужный)

ekkis
23 ноября 2012 в 22:22
4

единственная проблема с этим подходом (который я использую все время) заключается в том, что вы не можете его встроить

Tian van Heerden
4 марта 2016 в 09:15
2

Чтобы избавиться от начального пробела, измените запрос на SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ' ' END + Name FROM Names

Vita1ij
18 марта 2016 в 10:49
0

Кроме того, вы должны проверить, что Имя не равно нулю, вы можете сделать это, выполнив: SELECT @names = @names + ISNULL(' ' + Name, '')

avatar
Darryl Hein
12 октября 2008 в 00:10
140

В 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
hardmooth
14 февраля 2018 в 09:25
0

Работает в принципе. Две вещи, которые следует учитывать: 1) если ваш столбец не является CHAR, вам необходимо его преобразовать, например через GROUP_CONCAT( CAST(id AS CHAR(8)) ORDER BY id ASC SEPARATOR ',') 2) если у вас будет много значений, вы должны увеличить group_concat_max_len, как написано в coderhelper.com/a/1278210/1498405

avatar
Manu
11 октября 2008 в 23:57
2

Один из способов сделать это в SQL Server - это вернуть содержимое таблицы как XML (для необработанного XML), преобразовать результат в строку и затем заменить теги на «,».