考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
我通常使用这样的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或更高版本
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)
;
这对我有用(SQL Server 2016):
SELECT CarNamesString = STUFF((
SELECT ',' + [Name]
FROM tbl_cars
FOR XML PATH('')
), 1, 1, '')
来源如下:https://www.mytecbits.com/
还有一个针对MySQL的解决方案(因为该页面显示在Google for MySQL中):
SELECT [Name],
GROUP_CONCAT(DISTINCT [Name] SEPARATOR ',')
FROM tbl_cars
来自MySQL文档。
PostgreSQL数组非常棒。例子:
创建一些测试数据:
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)
DONE
由于PostgreSQL 9.0,引用删除的答案“没有名字的马”更容易:
select string_agg(name, ',')
from names;
对于Oracle DB,请参阅以下问题:如何在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文档提供了如何做到这一点的指南。
使用“TABLE”类型非常容易。让我们假设您的表名为Students,并且它具有列名。
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
此示例使用SQL Server 2008 R2进行了测试。