Comma Separated Names Based On Company ID
I have the following table of employee details
EmployeeName CompayId CompanyLastActive
---------------------------------------------------------
robort 112 10 Jun 2015 09:30
john 113 11 Jun 2015 11:10
sunny 114 14 Jun 2015 16:10
sumanth 114 15 Jun 2015 18:11
usha 115 07 Jun 2015 13:14
sudheer 115 14 Jun 2015 17:10
sweety 115 08 Jun 2015 16:34
I need to get the last working time of an employee based on CompanyID with the name EmployeeName separated by comma as shown below
EmployeeName CompayId CompanyLastActive
---------------------------------------------------------
robort 112 10 Jun 2015 09:30
john 113 11 Jun 2015 11:10
sunny, sumanth 114 15 Jun 2015 18:11
usha, sudheer, sweety 115 14 Jun 2015 17:10
please help me how to solve.
+3
source to share
3 answers
SELECT EmployeeName = STUFF((
SELECT ',' + e1.EmployeeName
FROM dbo.Employee e1
WHERE e.CompayId = e1.CompayId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
e.CompayId,
MAX(CompanyLastActive) as CompanyLastActive
FROM dbo.Employee e
GROUP BY e.CompayID
ORDER BY e.CompayId
Result:
EmployeeName CompayId CompanyLastActive
-------------------------------------------------------
robort 112 June, 10 2015 09:30:00
john 113 June, 11 2015 11:10:00
sunny,sumanth 114 June, 15 2015 18:11:00
usha,sudheer,sweety 115 June, 14 2015 17:10:00
Sample result in SQL Fiddle .
+4
source to share
Check it out, I have not tested this as I feel lazy to build the circuit, it will probably throw the group by mistake, you can handle it
SELECT
Results.CompayId,
STUFF((
SELECT ', ' + CAST(EmployeeName AS VARCHAR(MAX))
FROM YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
,max(Results.CompanyLastActive) as CompanyLastActive
FROM YourTable Results
GROUP BY CompayId
+3
source to share
You can use this query:
SELECT EmployeeNames = dbo.EmployeeNamesPerCompany(CompanyID,', '),
CompanyID,
CompanyLastActive = MAX(CompanyLastActive)
FROM Employee e
GROUP BY CompanyID
ORDER BY MAX(CompanyLastActive)
If you've created a scalar function like:
CREATE FUNCTION [dbo].[EmployeeNamesPerCompany]
(
@companyID Int,
@delimiter varchar(5)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + @delimiter, '') + e.EmployeeName
FROM Employee e
WHERE e.CompanyId = @companyID
ORDER BY e.EmployeeName
return @Names
END
+2
source to share