考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
我在家里无法访问SQL Server,所以我猜测这里的语法,但大致上是这样的:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
其他回答
提出了递归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)
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)
;
在Oracle中,它是wm_concat。我相信这个功能在10g及更高版本中可用。
在SQL Server vNext中,这将内置STRING_AGG函数。在STRING_AGG(Transact-SQL)中了解有关它的更多信息。
在SQL Server中实现这一点的一种方法是将表内容返回为XML(对于XML原始),将结果转换为字符串,然后用“,”替换标记。