Click here to Skip to main content
14,878,761 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted

Hi,

Solution 1 : COALESCE
SQL
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


Solution 2 : XML Path
SQL
Select [Employee Name] + ',' AS [text()]
From THE_TABLE
For XML PATH ('')
   
In addition to solution 1, this article[^] discusses various methods for concatenating row values in SQL. For example:
SQL
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
;
   
Comments
Bittu14 19-Dec-15 3:29am
   
STUFF and FOR XML is not working in SQL Developer software..
Richard Deeming 4-Jan-16 8:10am
   
And that's why you should always include the name and version of the DBMS you're using in the question! :)

STUFF and FOR XML work perfectly well in Microsoft SQL Server 2005 or later. If you're using a different DBMS, then the answer will be different.
Bittu14 19-Dec-15 3:30am
   
I have used listagg tag for merging.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900