SELECT
Table_Name
,STUFF((
SELECT ',' + Column_Name
FROM INFORMATION_SCHEMA.Columns Columns
WHERE Tables.Table_Name = Columns.Table_Name
ORDER BY Column_Name
FOR XML PATH ('')), 1, 1, ''
)Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME
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
)
);
Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers
SELECT @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM TableName where Number IS NOT NULL
SELECT @Numbers
当我尝试使用一对多关系加入两个表时,我遇到了类似的问题 . 在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]
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
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
4
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)
;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
221
在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
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
106
这也很有用
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
17
如果要处理空值,可以通过添加where子句或在第一个周围添加另一个COALESCE来实现 .
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People
17
当存在ORDER BY子句时,此答案可能会返回意外结果 . 要获得一致的结果,请使用其他答案中详述的FOR XML PATH方法之一 .
使用 COALESCE :
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
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
53
在SQL Server 2005中
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
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
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.
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')
这是一个示例:
DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary
30 回答
在SQL Server vNext中,这将使用STRING_AGG函数构建,请在此处阅读更多相关信息:https://msdn.microsoft.com/en-us/library/mt790580.aspx
MySQL complete Example:
我们有可以拥有许多数据的用户,我们希望有一个输出,我们可以在列表中看到所有用户数据:
Result:
Table Setup:
Query:
对于其他答案,阅读答案的人必须知道特定的域表,例如车辆或学生 . 必须创建表并填充数据以测试解决方案 .
下面是一个使用SQL Server“Information_Schema.Columns”表的示例 . 通过使用此解决方案,不需要创建表或添加数据 . 此示例为数据库中的所有表创建逗号分隔的列名列表 .
在Oracle中,它是
wm_concat
. 我相信这个功能在10g release和更高版本中可用 .此方法仅适用于Teradata Aster数据库,因为它使用其NPATH函数 .
再次,我们有表学生
然后使用NPATH,它只是单个SELECT:
结果:
这个答案需要服务器中的某些特权才能工作 .
Assemblies对你来说是个不错的选择 . 有很多网站可以解释如何创建它 . 我认为很好解释的是one
如果你愿意,我已经创建了程序集,并且可以下载DLL here .
下载后,您需要在SQL Server中运行以下脚本:
注意服务器可以访问程序集的路径 . 由于您已成功完成所有步骤,因此可以使用以下功能:
希望能帮助到你!!!
使用XML帮助我用逗号分隔行 . 对于额外的逗号,我们可以使用SQL Server的替换功能 . 使用AS'data()'代替添加逗号将使用空格连接行,以后可以用逗号替换为下面的语法 .
在MS SQL Server中通过
XML
data()
命令尚未显示的一种方法是:假设名为NameList的表有一列名为FName,
收益:
只需要处理额外的逗号 .
Edit: 从@NReilingh的评论中采用,您可以使用以下方法删除尾随逗号 . 假设相同的表和列名称:
使用 COALESCE - Learn more from here
举个例子:
然后在sql server中写下面的代码,
输出将是:
从PostgreSQL 9.0开始,这很简单:
在9.0之前的版本中,可以使用
array_agg()
,如hgmnz所示您需要创建一个变量来保存最终结果并选择进入它,就像这样 .
最简单的解决方案
如果您使用的是SQL Server 2017或Azure,请参阅Mathieu Renda answer .
当我尝试使用一对多关系加入两个表时,我遇到了类似的问题 . 在SQL 2005中,我发现
XML PATH
方法可以非常容易地处理行的串联 .如果有一个名为
STUDENTS
的表我预期的结果是:
我使用了以下
T-SQL
:如果你可以在开头连接逗号并使用
substring
跳过第一个逗号,那么你可以以更紧凑的方式做同样的事情,这样你就不需要做一个子查询:对于Oracle DB,请参阅此问题:How can multiple rows be concatenated into one in Oracle without creating a stored procedure?
最佳答案似乎是@Emmanuel,使用Oracle 11g第2版及更高版本中提供的内置LISTAGG()函数 .
正如@ user762952指出的那样,根据Oracle的文档http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php,WM_CONCAT()函数也是一个选项 . 它似乎很稳定,但Oracle明确建议不要将它用于任何应用程序SQL,因此使用风险自负 .
除此之外,你必须编写自己的功能;上面的Oracle文档提供了如何执行此操作的指南 .
SQL Server 2017和SQL Azure:STRING_AGG
从下一版本的SQL Server开始,我们最终可以跨行连接,而不必诉诸任何变量或XML witchery .
STRING_AGG (Transact-SQL)
Without grouping
With grouping :
With grouping and sub-sorting
我真的很喜欢Dana's answer的优雅 . 只是想让它完整 .
Postgres数组非常棒 . 例:
创建一些测试数据:
将它们聚合在一个数组中:
将数组转换为逗号分隔的字符串:
DONE
从PostgreSQL 9.0开始,它就是even easier .
建议使用递归CTE解决方案,但不提供代码 . 下面的代码是一个递归CTE的例子 - 注意虽然结果与问题匹配,但数据与给定的描述不完全匹配,因为我认为你真的想要这样做行组,而不是表中的所有行 . 更改它以匹配表中的所有行是留给读者的练习 .
在SQL Server 2005及更高版本中,使用下面的查询来连接行 .
这使得流浪逗号开头 .
但是,如果您需要其他列,或者要CSV子表,则需要将其包装在标量用户定义字段(UDF)中 .
您也可以在SELECT子句中使用XML路径作为相关子查询(但是我必须等到我重新开始工作,因为Google不在家做工作:-)
我在家里无法访问SQL Server,所以我猜这里的语法,但它或多或少:
这也很有用
回报
如果要处理空值,可以通过添加where子句或在第一个周围添加另一个COALESCE来实现 .
使用
COALESCE
:只是一些解释(因为这个答案似乎得到相对规律的观点):
1)无需使用空字符串值初始化
@Names
.2)最后无需剥去额外的分离器 .
@Names
为NULL,并且下一行将作为空字符串再次重新开始 . 容易修复一个两个解决方案:要么:
根据您想要的行为(第一个选项只过滤掉NULL,第二个选项将它们保留在列表中,并带有标记消息[将'N/A'替换为适合您的任何内容]) .
要避免空值,可以使用CONCAT()
我通常使用select这样来连接SQL Server中的字符串:
在SQL Server 2005中
在SQL Server 2016中
你可以使用FOR JSON syntax
即
结果将成为
这甚至会使您的数据包含无效的XML字符
'"},{"_":"'
是安全的,因为如果您的数据包含'"},{"_":"',
,它将被转义为"},{\"_\":\"
您可以将
', '
替换为任何字符串分隔符在SQL Server 2017中,Azure SQL数据库
你可以使用新的STRING_AGG function
一个随时可用的解决方案,没有额外的逗号:
空列表将导致NULL值 . 通常,您将列表插入表列或程序变量:根据需要调整255最大长度 .
(Diwakar和Jens Frandsen提供了很好的答案,但需要改进 . )
在MySQL中有一个函数GROUP_CONCAT(),它允许您连接多行的值 . 例:
Oracle 11g第2版支持LISTAGG功能 . 文件here .
警告
如果结果字符串可能超过4000个字符,请小心实现此功能 . 它会引发异常 . 如果是这种情况,那么您需要处理异常或滚动自己的函数,以防止连接的字符串超过4000个字符 .
这是一个示例: