Concatenate column values into single row using stuff and xml path

Stuff
It is used to insert a string into another string, It removes specified length of characters from the start of the string and then add second string at the start in the first string.

syntax:
stuff(expression, start, length, replace with these string)

Example

select stuff('SQL Server',5,6,'Database')

Output: 

XML Path()

Here it is used to convert column data into single row

Concatenate column values into single row

Suppose we have a situation in which we need to show column values concatenated on the basis of aggregate function applied on them.

for example:
show all the employee name whose salary sum is combined on the basis of there location and designation.

Creating tables

1. EmployeeMaster

2. EmployeeLocationDetails

3. EmployeeDesignationDetails

create table employeeMaster

(

EmpId int primary key,

EmpName varchar(100),

DesignationId int,

LocationId int,

Salary int

)

Create table employeeDesignationDetails

(

DesignationId int primary key identity(1,1),

DesignationName varchar(100)

)

Create table employeeLocationDetails

(

LocationId int primary key identity(1,1),

LocationName varchar(100)

)

 inserting into Tables

insert into employeeLocationDetails(LocationName)

values ('Delhi'),('Banglore')

insert into employeeDesignationDetails(DesignationName)

values ('Developer'),('Designer'),('Manager')

insert into employeeMaster(EmpName,DesignationId,LocationId,Salary)

values ('Ajay',2,1,45000),('Akhshay',2,1,35000),('Sanjay',1,2,34000),('Tarun',1,2,56000)

,('Anita',1,1,33000),('Aanchal',1,1,28000),('Aaina',2,2,27000),('Preeti',2,2,45000)

,('Rajiv',3,1,46000),('Neeraj',3,1,78000)

For achieving above goal we will be needing a temporary temporary

select a.EmpName,Salary,LocationName,a.LocationId,DesignationName,a.DesignationId into #temp from employeeMaster a

left outer join employeeLocationDetails b on a.LocationId=b.LocationId

left outer join employeeDesignationDetails c on c.DesignationId=a.DesignationId

select

stuff((select ', ' + EmpName from #temp a where a.LocationId=b.LocationId and a.DesignationId=b.DesignationId for xml path('')),1,2,'') as EmpName,

sum(Salary) 'Salary',DesignationName,LocationName

 from #temp b

 group By LocationName,DesignationName,LocationId,DesignationId