考虑一个包含姓名的数据库表格,有三行:
Peter
Paul
Mary
有没有简单的方法将这些内容转换成一个字符串:
Peter, Paul, Mary
?考虑一个包含姓名的数据库表格,有三行:
Peter
Paul
Mary
Peter, Paul, Mary
?除了Chris Shaffer的回答之外:
如果您的数据可能会重复,例如
Tom
Ali
John
Ali
Tom
Mike
使用DISTINCT可以避免重复,得到 Tom,Ali,John,Mike
,而不是 Tom,Ali,John,Ali,Tom,Mike
:
DECLARE @Names VARCHAR(8000)
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names
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
GROUP BY
的重要性。 - JoshDECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name)
FROM Names
select @names
我非常欣赏Dana的回答的优雅,只是想让它更完整。
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
,然后您就不必之后截断它了。 - Tian van HeerdenDECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
这将把杂乱的逗号放在开头。
然而,如果你需要其他列,或者将子表格转换为CSV格式,你需要将其包装在标量用户定义字段(UDF)中。
你也可以在SELECT子句中使用XML路径作为相关子查询(但我必须等到回到工作岗位,因为Google不支持家庭工作内容 :-))
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
自 SQL Server 2017 起,可以使用 STRING_AGG 函数。
如果你想处理null值,可以通过添加where子句或在第一个COALESCE周围添加另一个COALESCE来实现。
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People
我通常在SQL Server中使用如下的select语句来拼接字符串:
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
针对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