Merge multiple rows into Single rows

I was asked a very good question from my friend, how can one merge multiple rows into single rows with comma separated output.

Thought entertaining, this means breaking the rule of Normalization 1NF, still if there is business need one has to compromise with it.

There I device two way to achieve the solution for it. Before we discuss solution, let’s first try to stimulate the same problem.

For our example I am taking two table say, deptTest and emptest , lets create these two tables.

create table deptTest

(dept_id int,

dept_name varchar(10))

Command(s) completed successfully.

create table empTest

(emp_id int,

name varchar(10),

dept_id int);

Command(s) completed successfully.

 Now let’s insert some records in both the table.

insert into deptTest values(1,'Software'),(2,'Operation')

(2 row(s) affected)

insert into empTest values(1,'Snehal',1),(2,'Prachi',1),(3,'Bratiti',2)

(3 row(s) affected)

Lets query both the table

select * from deptTest

dept_id     dept_name

----------- ----------

1           Software

2           Operation

(2 row(s) affected)

select * from empTest

emp_id      name       dept_id

----------- ---------- -----------

1           Snehal     1

2           Prachi     1

3           Bratiti    2

(3 row(s) affected)

Problem Statement: we need to find all employees in comma separated values for each department. Like this

name

----------------

Bratiti

Snehal,Prachi

First Method: with XML and Path

Here in the below script, it is performing co related query, for each department in the outer query, the inner query is trying to find employees for the department.

The inner query result is then converted into XML and with the help of data()we are formating values into normal text.

  

select distinct t1.dept_name, name =

replace((SELECT t2.name AS [data()] FROM empTest t2 where t2.dept_id = t1.dept_id FOR XML PATH('')), ' ', ',')

from deptTest t1

Second Method:

Here we are creating a function where we are appending each record in a particular string and then returning the string to the calling function.

create function fn_Merge(@id int)

returns varchar(20)

as

begin

      declare @name1 varchar(20)

      set @name1=''

      select @name1 = @name1 + ',' + t.name

      from empTest t where t.dept_id = @id

      return substring(@name1,2,len(@name1))

end

select dbo.fn_Merge(d.dept_id) as Name from deptTest d

Name

--------------------

Snehal,Prachi

Bratiti

(2 row(s) affected)

For a programmer, the surest lane to wisdom is the noninterventionist coding. Let him do code only.   

Post Reference: Vikram Aristocratic Elfin Share