考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
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
其他回答
虽然为时已晚,而且已经有了许多解决方案。下面是MySQL的简单解决方案:
SELECT t1.id,
GROUP_CONCAT(t1.id) ids
FROM table t1 JOIN table t2 ON (t1.id = t2.id)
GROUP BY t1.id
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
这会在开头加上不连贯的逗号。
但是,如果您需要其他列,或者需要CSV子表,则需要将其包装在标量用户定义字段(UDF)中。
您也可以在SELECT子句中使用XML路径作为相关子查询(但我必须等到回去工作,因为Google在家里不做工作:-)
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)
;
你需要创建一个变量来保存你的最终结果并选择它,就像这样。
最简单的解决方案
DECLARE @char VARCHAR(MAX);
SELECT @char = COALESCE(@char + ', ' + [column], [column])
FROM [table];
PRINT @char;
SQL Server中尚未通过XML data()命令显示的一个方法是:
假设一个名为NameList的表有一列名为FName,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
返回:
"Peter, Paul, Mary, "
只需处理额外的逗号。
正如@NReilingh的注释所采用的,您可以使用以下方法删除尾随逗号。假设表和列名相同:
STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands