How Can I Combine Multiple Rows into a Single Column with Comma-Separated Values Using T-SQL?

Returning multiple values ​​in a single column in T-SQL

Getting multiple values ​​from different rows and merging them into a single column is a common task in a SQL Server environment. This is particularly useful in scenarios such as retrieving all aliases associated with a specific user.

To achieve this efficiently, consider using a custom function that utilizes the COALESCE function. This approach allows you to progressively concatenate the retrieved values ​​into a single string, ultimately providing the desired output.

Here is an example of a function called GetAliasesById:

<code class="language-sql">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</code>

After defining this function, you can integrate it into your query to get the desired output:

<code class="language-sql">SELECT UserID, dbo.GetAliasesByID(UserID)
FROM UserAliases
GROUP BY UserID</code>

This query will return a table with two columns: UserID and Aliases. The Aliases column will contain a comma-separated list of all aliases associated with each user, meeting the specified requirement of returning multiple values ​​in a single column.

The above is the detailed content of How Can I Combine Multiple Rows into a Single Column with Comma-Separated Values Using T-SQL?. For more information, please follow other related articles on the PHP Chinese website!