下面以SQLServer2005自带的AdventureWorks数据库中的2个表为例
HumanResources.Department --部门信息表
HumanResources.EmployeeDepartmentHistory --部门员工对照表
--使用自定义函数的方式
SELECT groupname,dbo.fn_concatemployees(departmentid) employeesconcat FROM HumanResources.Department CREATE FUNCTION fn_concatemployees(@departmentid INT) RETURNS VARCHAR(1000) AS BEGIN DECLARE @employeeidcancat AS VARCHAR(1000); SET @employeeidcancat=''; SELECT @employeeidcancat=@employeeidcancat+CAST(employeeid AS VARCHAR(10))+';' FROM HumanResources.EmployeeDepartmentHistory WHERE departmentid=@departmentid; RETURN @employeeidcancat; END |
--使用SQLServer2005的FOR XML PATH方式
SELECT groupname, (SELECT CAST(o.employeeid AS VARCHAR(10))+';' AS [text()] FROM HumanResources.EmployeeDepartmentHistory o WHERE o.departmentid=c.departmentid ORDER BY employeeid FOR XML PATH('')) AS employeesconcat FROM HumanResources.Department c |
--在Oracle10g中采用wmsys.wm_concat方式
SELECT groupname,wmsys.wm_concat(employeeid) employeesconcat FROM EmployeeDepartmentHistory GROUP BY groupname; |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6517/viewspace-629371/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/6517/viewspace-629371/