如何在一列中返回多个值（T-SQL）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/122942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return multiple values in one column (T-SQL)?
提问by roman m
I have a table UserAliases(UserId, Alias) with multiple aliases per user. I need to query it and return all aliases for a given user, the trick is to return them all in one column.
我有一个表UserAliases( UserId, Alias)，每个用户有多个别名。我需要查询它并返回给定用户的所有别名，诀窍是将它们全部返回在一列中。
Example:
例子：
UserId/Alias
1/MrX
1/MrY
1/MrA
2/Abc
2/Xyz
I want the query result in the following format:
我想要以下格式的查询结果：
UserId/Alias
1/ MrX, MrY, MrA
2/ Abc, Xyz
Thank you.
谢谢你。
I'm using SQL Server 2005.
我正在使用 SQL Server 2005。
p.s. actual T-SQL query would be appreciated :)
ps 实际的 T-SQL 查询将不胜感激:)
回答by Scott Nichols
You can use a function with COALESCE.
您可以将函数与 COALESCE 一起使用。
CREATE FUNCTION [dbo].[GetAliasesById]
(
@userID int
)
RETURNS varchar(max)
AS
BEGIN
declare @output varchar(max)
select @output = COALESCE(@output + ', ', '') + alias
from UserAliases
where userid = @userID
return @output
END
GO
SELECT UserID, dbo.GetAliasesByID(UserID)
FROM UserAliases
GROUP BY UserID
GO
回答by leoinfo
Well... I see that an answer was already accepted... but I think you should see another solutions anyway:
嗯......我看到一个答案已经被接受......但我认为你应该看到另一个解决方案：
/* EXAMPLE */
DECLARE @UserAliases TABLE(UserId INT , Alias VARCHAR(10))
INSERT INTO @UserAliases (UserId,Alias) SELECT 1,'MrX'
UNION ALL SELECT 1,'MrY' UNION ALL SELECT 1,'MrA'
UNION ALL SELECT 2,'Abc' UNION ALL SELECT 2,'Xyz'
/* QUERY */
;WITH tmp AS ( SELECT DISTINCT UserId FROM @UserAliases )
SELECT
LEFT(tmp.UserId, 10) +
'/ ' +
STUFF(
( SELECT ', '+Alias
FROM @UserAliases
WHERE UserId = tmp.UserId
FOR XML PATH('')
)
, 1, 2, ''
) AS [UserId/Alias]
FROM tmp
/* -- OUTPUT
UserId/Alias
1/ MrX, MrY, MrA
2/ Abc, Xyz
*/
回答by Mladen
this is one of the fastest and simplest ways to do what you need without the need for a UDF: http://weblogs.sqlteam.com/mladenp/archive/2007/06/01/60220.aspx
这是无需 UDF 即可完成所需操作的最快和最简单的方法之一：http: //weblogs.sqlteam.com/mladenp/archive/2007/06/01/60220.aspx
there's one other way using a numbers table that is faster for really large datasets but i don't think you'll need that.
对于非常大的数据集，还有另一种使用数字表的方法，但我认为您不需要它。
回答by Mike McAllister
Have a look at this thread already on StackOverflow, it conveniently gives you a T-SQL example.
看看StackOverflow 上已有的这个线程，它方便地为您提供了一个 T-SQL 示例。
回答by Ryan Rinaldi
My boss wrote up an article on this way back 2003: Concatenation with COALESCE
我的老板在 2003 年就这样写了一篇文章：Concatenation with COALESCE
回答by Tushar Maru
DECLARE @Str varchar(500)
SELECT @Str=COALESCE(@Str,'') + CAST(ID as varchar(10)) + ','
FROM dbo.fcUser
SELECT @Str
回答by Eric Z Beard
You can either loop through the rows with a cursor and append to a field in a temp table, or you could use the COALESCE function to concatenate the fields.
您可以使用游标遍历行并附加到临时表中的字段，或者您可以使用 COALESCE 函数来连接字段。
回答by CodeRedick
Sorry, read the question wrong the first time. You can do something like this:
对不起，第一次读错了问题。你可以这样做：
declare @result varchar(max)
--must "initialize" result for this to work
select @result = ''
select @result = @result + alias
FROM aliases
WHERE username='Bob'
回答by gehsekky
group_concat() sounds like what you're looking for.
group_concat() 听起来像你在找什么。
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
since you're on mssql, i just googled "group_concat mssql" and found a bunch of hits to recreate group_concat functionality. here's one of the hits i found:
因为您使用的是 mssql，所以我只是在 google 上搜索了“group_concat mssql”并找到了一些重新创建 group_concat 功能的点击。这是我找到的热门歌曲之一：
