连接行值

发布于 2024-08-17 11:32:15 字数 414 浏览 4 评论 0原文

我使用的是 Microsoft SQL Server 2005，并且能够根据以下查询连接行值：

SELECT e1.EMP_ID,
( SELECT cast(Sector_ID as varchar(10)) + ';'
FROM Employee_Sector_relationship e2
WHERE e2.Emp_ID = e1.Emp_ID
ORDER BY Sector_ID
FOR XML PATH('') ) AS Sectors
FROM Employee_Sector_Relationship e1
GROUP BY Emp_ID

但它在 Microsoft Server 2000 中不起作用。它在 for 关键字附近给我一个错误。谁能帮我连接 Microsoft Server 2000 中的行值？

原文

I was using Microsoft SQL server 2005 and was able to concatenate row values based on the following query:

SELECT e1.EMP_ID,
( SELECT cast(Sector_ID as varchar(10)) + ';'
FROM Employee_Sector_relationship e2
WHERE e2.Emp_ID = e1.Emp_ID
ORDER BY Sector_ID
FOR XML PATH('') ) AS Sectors
FROM Employee_Sector_Relationship e1
GROUP BY Emp_ID

But it doesn't work in Microsoft Server 2000. It gives me an error near the for keyword.
Can anyone help me to concatenate the row values in Microsoft Server 2000?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

假情假意假温柔 2024-08-24 11:32:15

这是一种应该对您有用的技术。如果您愿意，可以在一个批处理语句中执行此操作：

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

有关详细信息，请参阅使用 COALESCE 构建逗号分隔字符串。

This is a technique that should work for you. You can execute this in one batch statement if you wish:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

For more information, see Using COALESCE to Build Comma-Delimited String.

回复收藏 0 原文