Comma Separated List as part of a SQL SELECT Results
I would like to return a list of Employees, various information about them and a column with the list of the top 3 projects they have hours assigned to them each month in a comma separated list. I have a select statement that returns the hours successfully, but I'm not sure how to embed it into a select statemnet. Below is the select statement. I am trying to do this in SQL Server.
DECLARE @Projects varchar(max)
SELECT Top(3) @Projects = COALESCE(@Projects+', ' ,'') + ltrim(rtrim(Projects.Title))
FROM EmployeeProjectPlanning INNER JOIN
Projects ON EmployeeProjectPlanning.ProjectID = Projects.ProjectID
WHERE (EmployeeProjectPlanning.EmpID = 1) AND (EmployeeProjectPlanning.MonthID开发者_Python百科 = 9) AND (EmployeeProjectPlanning.Year = 2011)
ORDER BY EmployeeProjectPlanning.Hours DESC
SELECT @Projects
Assuming you are using SQL 2005 or higher, you can use FOR XML
to create the comma separated list in one field. Just put the following in your SELECT
statement where you need the projects column:
SUBSTRING(
(SELECT TOP (3) ','+ltrim(rtrim(Projects.Title))
FROM EmployeeProjectPlanning AS emp
INNER JOIN Projects as proj ON emp.ProjectID = proj.ProjectID
WHERE (emp.EmpID = 1)
AND (emp.MonthID = 9)
AND (emp.Year = 2011)
ORDER BY emp.Hours DESC
FOR XML PATH('')
),2,8000) AS Projects
精彩评论