sql-server - texto - separar por comas en sql

nombres separados por comas basados ​​en la identificación de la compañía

Question

Tengo la siguiente tabla de detalles del empleado

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

Necesito obtener el último tiempo activo de empleado basado en CompanyID con EmployeeName separado por comas como a continuación

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

por favor, ayúdame a resolver.

Answer 1

Puedes usar esta consulta:

SELECT EmployeeNames = dbo.EmployeeNamesPerCompany(CompanyID,'', ''),
CompanyID,
CompanyLastActive = MAX(CompanyLastActive)
FROM Employee e
GROUP BY CompanyID
ORDER BY MAX(CompanyLastActive)

Si ha creado una función de valor escalar como esta:

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

Demo Sql-Fiddle

Answer 2

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

Resultado:

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

Resultado de la muestra en SQL Fiddle .

Answer 3

Mira esto, no lo he comprobado ya que me siento perezoso para construir el esquema, probablemente lanzaría un grupo por error, puedes manejarlo

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