TA貢獻1859條經驗 獲得超6個贊
您可以使用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
TA貢獻1829條經驗 獲得超13個贊
嗯......我看到答案已被接受......但我認為你應該看到另一種解決方案：
/* 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
*/
舉報