考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
使用递归查询,您可以执行以下操作:
-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;
-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');
-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank where rang=1
union all
select f0.*, cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1
)
select AllName from tmpRecursive
where rang=NbRow
其他回答
使用递归查询,您可以执行以下操作:
-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;
-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');
-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank where rang=1
union all
select f0.*, cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1
)
select AllName from tmpRecursive
where rang=NbRow
提出了递归CTE解决方案,但没有提供代码。下面的代码是递归CTE的示例。
请注意,虽然结果与问题相符,但数据与给定的描述并不完全相符,因为我假设您确实希望对行组(而不是表中的所有行)执行此操作。将其更改为与表中的所有行相匹配是读者的练习。
;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
OPTION (MAXRECURSION 101)
使用COALESCE-从这里了解更多信息
例如:
102103104
然后在SQL Server中编写以下代码,
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
输出将是:
102,103,104
使用XML帮助我用逗号分隔行。对于额外的逗号,我们可以使用SQL Server的replace函数。使用AS“data()”将用空格连接行,而不是添加逗号,稍后可以用逗号替换,语法如下所示。
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
一个现成的解决方案,没有额外的逗号:
select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"
空列表将导致NULL值。通常,您会将列表插入到表列或程序变量中:根据需要调整最大长度255。
(迪瓦卡尔和延斯·弗兰森提供了很好的答案,但需要改进。)