Преобразование нескольких строк в одну с запятой в качестве разделителя
Если я выпущу SELECT username FROM Users Я получаю этот результат:
имя пользователя -------- Павел Джон Мэри
но что мне действительно нужно, так это одна строка со всеми значениями, разделенными запятой, например:
Пол, Джон, Мэри
Как мне это сделать?
10 ответов
Это должно работать для вас. Протестировано вплоть до SQL 2000.
create table #user (username varchar(25))
insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')
declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user
select SUBSTRING(@tmp, 0, LEN(@tmp))
select
distinct
stuff((
select ',' + u.username
from users u
where u.username = username
order by u.username
for xml path('')
),1,1,'') as userlist
from users
group by username
раньше была опечатка, вышеперечисленные работы
Хороший обзор нескольких подходов:
Копия статьи -
Coalesce не является ответом на конкатенацию строк в T-SQL. За последние годы я видел много публикаций об использовании функции COALESCE для работы конкатенации строк в T-SQL. Это один из примеров здесь (заимствовано у Readifarian Marc Ridey).
DECLARE @categories varchar(200)
SET @categories = NULL
SELECT @categories = COALESCE(@categories + ',','') + Name
FROM Production.ProductCategory
SELECT @categories
Этот запрос может быть достаточно эффективным, но необходимо соблюдать осторожность и правильно понимать использование COALESCE. COALESCE - это версия ISNULL, которая может принимать более двух параметров. Возвращает первое в списке параметров, которое не является нулевым. Так что на самом деле это не имеет ничего общего с конкатенацией, и следующий фрагмент кода точно такой же - без использования COALESCE:
DECLARE @categories varchar(200)
SET @categories = ''
SELECT @categories = @categories + ',' + Name
FROM Production.ProductCategory
SELECT @categories
Но неупорядоченный характер баз данных делает это ненадежным. Вся причина, по которой у T-SQL (пока) нет функции сцепления, заключается в том, что это агрегат, для которого важен порядок элементов. Используя этот метод присвоения переменных конкатенации строк, вы можете обнаружить, что возвращаемый ответ не имеет всех значений, особенно если вы хотите, чтобы подстроки были расположены в определенном порядке. Рассмотрим следующее, которое на моей машине возвращает только "Аксессуары", когда я хотел, чтобы оно возвращалось "Велосипеды, Одежда, Компоненты, Аксессуары":
DECLARE @categories varchar(200)
SET @categories = NULL
SELECT @categories = COALESCE(@categories + ',','') + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)
SELECT @categories
Гораздо лучше использовать метод, который учитывает порядок и который был включен в SQL2005 специально для объединения строк - FOR XML PATH('')
SELECT ',' + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)
FOR XML PATH('')
В публикации, в которой я недавно сравнивал GROUP BY и DISTINCT при использовании подзапросов, я продемонстрировал использование FOR XML PATH(''). Посмотрите на это, и вы увидите, как это работает в подзапросе. Функция "STUFF" предназначена только для удаления запятой.
USE tempdb;
GO
CREATE TABLE t1 (id INT, NAME VARCHAR(MAX));
INSERT t1 values (1,'Jamie');
INSERT t1 values (1,'Joe');
INSERT t1 values (1,'John');
INSERT t1 values (2,'Sai');
INSERT t1 values (2,'Sam');
GO
select
id,
stuff((
select ',' + t.[name]
from t1 t
where t.id = t1.id
order by t.[name]
for xml path('')
),1,1,'') as name_csv
from t1
group by id
;
FOR XML PATH - это единственная ситуация, в которой вы можете использовать ORDER BY в подзапросе. Другой ТОП. И когда вы используете безымянный столбец и FOR XML PATH(''), вы получите прямую конкатенацию без тегов XML. Это означает, что строки будут закодированы в HTML, поэтому, если вы объединяете строки, которые могут иметь символ <(и т. Д.), То вам, возможно, следует исправить это позже, но в любом случае, это по-прежнему лучший способ объединения строк. в SQL Server 2005
Опираясь на Мвигдалс ответ. если вам также нужно сделать группировку здесь, как заставить ее выглядеть
group, csv
'group1', 'paul, john'
'group2', 'mary'
--drop table #user
create table #user (groupName varchar(25), username varchar(25))
insert into #user (groupname, username) values ('apostles', 'Paul')
insert into #user (groupname, username) values ('apostles', 'John')
insert into #user (groupname, username) values ('family','Mary')
select
g1.groupname
, stuff((
select ', ' + g.username
from #user g
where g.groupName = g1.groupname
order by g.username
for xml path('')
),1,2,'') as name_csv
from #user g1
group by g1.groupname
Вы можете использовать этот запрос для выполнения вышеуказанной задачи:
DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test
SELECT field2 = @test
Для получения подробной информации и пошагового объяснения посетите следующую ссылку http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html
DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
SELECT @EmployeeList
источник: http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
В SQLite это проще. Я думаю, что есть аналогичные реализации для MySQL, MSSql и Orable
CREATE TABLE Beatles (id integer, name string );
INSERT INTO Beatles VALUES (1, "Paul");
INSERT INTO Beatles VALUES (2, "John");
INSERT INTO Beatles VALUES (3, "Ringo");
INSERT INTO Beatles VALUES (4, "George");
SELECT GROUP_CONCAT(name, ',') FROM Beatles;
Вы можете использовать stuff() для преобразования строк в значения, разделенные запятыми
select
EmployeeID,
stuff((
SELECT ',' + FPProjectMaster.GroupName
FROM FPProjectInfo AS t INNER JOIN
FPProjectMaster ON t.ProjectID = FPProjectMaster.ProjectID
WHERE (t.EmployeeID = FPProjectInfo.EmployeeID)
And t.STatusID = 1
ORDER BY t.ProjectID
for xml path('')
),1,1,'') as name_csv
from FPProjectInfo
group by EmployeeID;
Спасибо @AlexKuznetsov за ссылку, чтобы получить этот ответ.
Чистое и гибкое решение в MS SQL Server 2005/2008 заключается в создании функции CLR Agregate.
Вы найдете довольно много статей (с кодом) в Google.
Похоже, эта статья проведет вас через весь процесс с использованием C#.
Если вы выполняете это через PHP, что по этому поводу?
$hQuery = mysql_query("SELECT * FROM users");
while($hRow = mysql_fetch_array($hQuery)) {
$hOut .= $hRow['username'] . ", ";
}
$hOut = substr($hOut, 0, strlen($hOut) - 1);
echo $hOut;