开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜