How to merge two row value into a single row

Question

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 18-Dec-15 3:03am

Bittu14

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

pjaar89 · Accepted Answer · 2015-12-18T03:44:00

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 ('')

Richard Deeming · Accepted Answer · 2015-12-18T04:07:00

Solution 2

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
;

Posted 18-Dec-15 4:07am

Richard Deeming

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.