Рассмотрим таблицу базы данных с именами из трех строк:
Peter
Paul
Mary
Есть ли простой способ превратить это в одну строку Peter, Paul, Mary
?
Если вы используете 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
<
или &
. Смотрите @ BenHinman комментарий. FOR XML PATH ('')
. Это означает, что он не должен считаться надежным, поскольку любое исправление или обновление может изменить его функционирование. Это в основном полагаться на устаревшую функцию. FOR XML
он предназначен для генерации XML, а не для конкатенации произвольных строк. Вот почему она ускользает &
, <
и >
кодов XML сущностей ( &
, <
, >
). Я предполагаю, что это также сбежит "
и '
в "
и '
в атрибутах также. Это не GROUP_CONCAT()
, string_agg()
, array_agg()
, listagg()
и т.д. , даже если вы можете отчасти сделать его сделать это. Мы должны тратить наше время, требуя, чтобы Microsoft реализовала правильную функцию. string_agg
в v.Next. и все это может уйти. Зависит от поставщика вашей базы данных. MySQL имеет concat_ws. MS SQL Server ожидает, что вы сделаете это в своем клиентском приложении.
Обновление: вы также можете сделать это во внешней процедуре или в UDF, возможно, используя курсор или вызывая код CLR.
Один из способов сделать это в SQL Server - вернуть содержимое таблицы в формате XML (для необработанного XML), преобразовать результат в строку и затем заменить теги на «,».
В 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
SEPARATOR '", "'
я пропущу некоторые символы в конце последней записи. почему это может случиться? CHAR
, вам нужно привести его, например, через GROUP_CONCAT( CAST(id AS CHAR(8)) ORDER BY id ASC SEPARATOR ',')
2) если у вас есть много значений, вы должны увеличить group_concat_max_len
как написано в stackoverflow.com/a/1278210/1498405У меня нет доступа к SQL Server дома, поэтому я предполагаю синтаксис здесь, но он более или менее:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ' ' END + Name FROM Names
SELECT @names = @names + ISNULL(' ' + Name, '')
Этот ответ может возвращать неожиданные результаты. Для получения согласованных результатов используйте один из методов FOR XML PATH, подробно описанных в других ответах.
Используйте 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' тем, что вам подходит]).
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
Это ставит случайную запятую в начале.
Однако, если вам нужны другие столбцы или для CSV дочерней таблицы, вам нужно обернуть это в скалярное пользовательское поле (UDF).
Вы можете использовать путь XML в качестве коррелированного подзапроса в предложении SELECT (но мне придется подождать, пока я не вернусь к работе, потому что Google не выполняет работу дома :-)
В 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
<
, >
, &
и т.д. , которые FOR XML PATH('')
будут автоматически избежать. Один метод, еще не показанный с помощью 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
+ ', '
он добавляет один пробел между каждым объединенным элементом. SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'')
Использование XML помогло мне получить строки, разделенные запятыми. Для дополнительной запятой мы можем использовать функцию замены SQL Server. Вместо добавления запятой использование AS data () объединит строки с пробелами, которые впоследствии можно будет заменить на запятые в качестве синтаксиса, приведенного ниже.
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
Как насчет этого:
ISNULL(SUBSTRING(REPLACE((select ',' FName as 'data()' from NameList for xml path('')), ' ,',', '), 2, 300), '') 'MyList'
Где «300» может быть любой ширины, принимая во внимание максимальное количество элементов, которые, по вашему мнению, будут отображаться.
В Oracle это так wm_concat
. Я считаю, что эта функция доступна в версии 10g и выше.
Я обычно использую 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
В 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
Мне очень понравилась элегантность ответа Даны . Просто хотел сделать это завершенным.
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ', ' END + Name FROM Names
потом не нужно было его усекать. Готовое решение без лишних запятых:
select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"
Пустой список приведет к значению NULL. Обычно вы вставляете список в столбец таблицы или переменную программы: установите максимальную длину 255 в соответствии с вашими потребностями.
(Дивакар и Йенс Франдсен дали хорошие ответы, но нуждаются в улучшении.)
', '
на, ','
если вам не нужно дополнительное место. 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 символов.
LISTAGG
работает отлично! Просто прочитайте документ, связанный здесь. wm_concat
удалено с версии 12с и далее. Было предложено рекурсивное решение 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
name
столбец в разделенных запятыми строку на 4 группы в id
с. На первый взгляд, я думаю, что это больше работы, чем большинство других решений для SQL Server. Массивы 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 это еще проще .
select array_to_string(array_agg(name||'('||id||')'
В оракуле есть несколько способов,
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
Начиная с PostgreSQL 9.0 это довольно просто:
select string_agg(name, ',')
from names;
В версиях до 9.0 array_agg()
можно использовать как показано hgmnz
SELECT string_agg(non_text_type::text, ',') FROM table
varchar
илиchar
Для БД 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 есть руководство, как это сделать.
Это тоже может быть полезно
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
Этот метод применяется только к базе данных 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]
С типом TABLE это очень просто. Давайте представим, что ваша таблица называется 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
Чтобы избежать нулевых значений, вы можете использовать CONCAT ()
DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name)
FROM Names
select @names
declare @phone varchar(max)=''
select @phone=@phone + mobileno +',' from members
select @phone
+', '
как хотел OP, а также вы не удаляете последний ';'. Я думаю, что этот ответ такой же, а также этот ответ ;). Null
, потому что вы начинаете с @phone IS Null
добавления и Null
будете Null
в SQL Server, я думаю, вы забыли что-то вроде добавления = ''
после первой строки;). Этот ответ потребует некоторых привилегий на сервере для работы.
Сборки - это хороший вариант для вас. Есть много сайтов, которые объясняют, как его создать. Один я думаю , что очень хорошо объяснил это один
Если вы хотите, я уже создал сборку, и можно скачать 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
Надеюсь, поможет!!!
MySQL complete Пример:
У нас есть Пользователи, у которых может быть много Данных, и мы хотим иметь вывод, где мы можем видеть все пользовательские Данные в списке:
Результат:
___________________________
| 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