How to Concatenate Multiple Rows into a Single Column in T-SQL?

Return multiple values ​​as a single column using T-SQL

When dealing with tables that contain multiple values ​​for a single attribute, these values ​​need to be retrieved and merged into a single column for efficient data analysis. This article dives into a solution that uses a combination of functions and string operations to return multiple values ​​in a column.

Example:

Consider the "UserAliases" table, with multiple aliases per user:

<code>UserId/Alias  
1/MrX  
1/MrY  
1/MrA  
2/Abc  
2/Xyz</code>

The goal is to convert the data into the following format:

<code>UserId/Alias  
1/ MrX, MrY, MrA  
2/ Abc, Xyz</code>

Solution:

For this we use the custom function "GetAliasesById" which uses the "COALESCE" function. This function initializes a variable "@output" and iteratively adds alias values ​​to it, separated by commas. The following is the function code:

<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>

Finally, we retrieve the data and apply the "GetAliasesById" function to merge the aliases:

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

This query returns the desired output with multiple aliases for each user combined into a single column. The "COALESCE" function handles null values ​​efficiently, ensuring valid string output.

The above is the detailed content of How to Concatenate Multiple Rows into a Single Column in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!