如何将两行值合并为一行 [英] How to merge two row value into a single row

问题描述

如何在sql开发人员软件中将多行组合成一行。



示例 - 这是具有多个值的列



ID Employee_Name

1 Arijit

1 Pradipta

1 Suvayu



我希望输出为 -



ID Employee_Name

1 Arijit，Pradipta，Suvayu

How i will combine multiple row into a single row in sql developer software.

example - this is the column which have multiple value

ID Employee_Name
1 Arijit
1 Pradipta
1 Suvayu

I want the output as -

ID Employee_Name
1 Arijit, Pradipta, Suvayu

推荐答案

解决方案1：COALESCE

Hi,

Solution 1 : COALESCE

CREATE FUNCTION [dbo].[GET_EMPLOYEES](@ID INT)
RETURNS NVARCHAR(250)
AS BEGIN
       DECLARE @EMPLOYEES NVARCHAR(250)

       SELECT @EMPLOYEES = COALESCE(@EMPLOYEES + ', ', '') + [Employee Name]
       FROM (
             SELECT DISTINCT [Employee Name]
             FROM THE_TABLE
             WHERE ID = @ID
       ) a

       RETURN @EMPLOYEES 
END
GO

解决方案2：XML路径

Solution 2 : XML Path

Select [Employee Name] + ',' AS [text()]
From THE_TABLE
For XML PATH ('')

除解决方案1外，本文 [ ^ ]讨论了在SQL中连接行值的各种方法。例如：

In addition to solution 1, this article[^] discusses various methods for concatenating row values in SQL. For example:

SELECT
    e1.Id,
    [Employee Name] = STUFF
    (
        (
            SELECT ', ' + [Employee Name]
            FROM YourTable As e2
            WHERE e2.Id = e1.Id
            ORDER BY [Employee Name]
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
    , 1, 2, '')
FROM
    YourTable As e1
GROUP BY
    Id
;

这篇关于如何将两行值合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！