I have two tables called tblEmployee and tblWorkPlace. The second table consists of multiple rows for each employee. I want to get the result as follows
EmployeeName WorkPlace
Gopi India,Pakistan,...
I.e. if tblWorkPlace consists of multiple rows for an employee, I want the result in a single row, with data being separated by commas.
How will I get this result ????
You'll need to have code on the client side for this. It is possible to make this happen in sql server, but it's not trivial, the performance is horrible, and this kind of thing does not belong on the database anyway.
You're not very clear on how the tables tblWorkplace and tblEmployee are connected - I'm assuming by means of a many-to-many link table or something.
If that's the case, you can use something like this:
SELECT
e.EmpName,
(SELECT STUFF(
(SELECT ',' + w.WPName
FROM @Workplace w
INNER JOIN @WorkplaceEmployeesLink we ON w.WorkplaceID = we.WorkplaceID
WHERE we.EmpID = e.EmpID
FOR XML PATH('')
), 1, 1, '')
) AS Workplaces
FROM @Employee e
(I've replaced your tables with my own table variables @Employee and @Workplace etc. for testing)
This gives me an output something like:
EmpName Workplaces
Gopi India,Pakistan
for that one row.
Basically, the internal FOR XML PATH('') selects the list of workplaces for each employee, and prepends each workplace with a ',' so you get something like ,India,Pakistan.
The STUFF method then stuff an empty string into that resulting string, at position one, for a length of 1 - essentially wiping out the first comma, thus you get the list of workplaces as you desired.
You can assign multiple values to a variable in a select statement. I call this "flattening" the records.
declare @WorkPlace varchar(max)
select @WorkPlace = '' -- it is important to be NOT null
select @WorkPlace = @WorkPlace + WorkPlace + ', '
from YourTable
where EmployeeName = 'Gopi'
You may want to add some code to remove the final ',' from the string.
Here's a link to different ways to get comma separated values.
