Concatenate column values on which aggregate function implemented in sql server

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 table variable

First we need to define the User defined table type that will represent the definition of a table.

CREATE TYPE employeeDetails AS TABLE

(

EmpId int,

EmpName varchar(100),

Salary int,

DesignationId int,

DesignationName varchar(100),

LocationId int ,

LocationName varchar(100)

)

Creating procedure

CREATE proc A_employeeConcatNamefromFunc

as

begin

Declare   @tmp as employeeDetails  --declaring temporary varible of table type

select e1.EmpId,e1.EmpName,e1.Salary,e2.DesignationId,e2.DesignationName,e3.LocationId,e3.LocationName into #temp from employeeMaster e1 --insertin into temporary table

left outer join employeeDesignationDetails e2 on e1.designationid=e2.designationid

left outer join employeeLocationDetails e3 on e3.locationid=e1.locationid

select  sum(e1.Salary) 'Salary',e2.DesignationId,e2.DesignationName,e3.LocationId,e3.LocationName into #temp1 from employeeMaster e1 --insertin into temporary table

left outer join employeeDesignationDetails e2 on e1.designationid=e2.designationid

left outer join employeeLocationDetails e3 on e3.locationid=e1.locationid

group by e2.DesignationId,e2.DesignationName,e3.LocationId,e3.LocationName

insert into @tmp select * from #temp --inserting into temporary varible

drop table #temp --droping temporary table

select dbo.func_EmployeeConcatName(a.DesignationId,a.LocationId,@tmp) as 'EmpName',DesignationName,LocationName,Salary 'Sum of Employee Salary' from #temp1 a

drop table #temp1

end

Creating function

CREATE function func_EmployeeConcatName(@DesignationId int,@LocationId int,@temp employeeDetails ReadOnly)--tempory table variable needs to be defined as read only.

returns varchar(max)

as

begin

declare @Name varchar(max)

select @Name= COALESCE(@Name+',' ,'') + EmpName from @temp where locationid=@LocationId and designationid=@DesignationId

return @Name

end