Если я выдаю SELECT username FROM Users, я получаю этот результат:
username -------- Paul John Mary
но мне действительно нужна одна строка со всеми значениями, разделенными запятой, например:
Paul, John, Mary
Как это сделать?
Преобразование нескольких строк в один с запятой в качестве разделителя
Ответ 1
Это должно сработать для вас. Протестировано вплоть до 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))
Ответ 2
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
имел опечатку раньше, описанные выше работы
Ответ 3
хороший обзор нескольких подходов:
Копия статьи -
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 еще не имеет функции concatenate, заключается в том, что это совокупность, для которой важна последовательность элементов. Используя этот метод контировки для контировки, вы можете фактически найти, что ответ, который возвращается, не имеет всех значений в нем, особенно если вы хотите, чтобы подстроки помещались в определенном порядке. Рассмотрим следующее, которое на моей машине возвращает "Аксессуары", когда я хотел, чтобы он возвращался "," Велосипеды, одежда, компоненты, аксессуары ":
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, поэтому, если вы объединяете строки, которые могут иметь < character (и т.д.), то вы, возможно, впоследствии исправите это, но в любом случае это лучший способ конкатенации строк в SQL Server 2005.
Ответ 4
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
Ответ 5
Вы можете использовать этот запрос для выполнения вышеуказанной задачи:
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
Ответ 6
построение на mwigdahls отвечает. если вам также нужно сделать группировку, вот как это сделать:
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
Ответ 7
Чистым и гибким решением в MS SQL Server 2005/2008 является создание функции Agregate CLR.
Вы найдете немало статей (с кодом) на google.
Похоже, в этой статье проведет вас через весь процесс с помощью С#.
Ответ 8
В 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;
Ответ 9
вы можете использовать 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 за ссылку, чтобы получить этот ответ.
Ответ 10
Если вы выполняете это через 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;