如何创建 SQL Server 函数来“加入"从子查询到单个分隔字段的多行? [英] How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?
问题描述
为了说明,假设我有如下两个表:
To illustrate, assume that I have two tables as follows:
VehicleID Name
1 Chuck
2 Larry
LocationID VehicleID City
1 1 New York
2 1 Seattle
3 1 Vancouver
4 2 Los Angeles
5 2 Houston
我想写一个查询来返回以下结果:
I want to write a query to return the following results:
VehicleID Name Locations
1 Chuck New York, Seattle, Vancouver
2 Larry Los Angeles, Houston
我知道这可以使用服务器端游标来完成,即:
I know that this can be done using server side cursors, ie:
DECLARE @VehicleID int
DECLARE @VehicleName varchar(100)
DECLARE @LocationCity varchar(100)
DECLARE @Locations varchar(4000)
DECLARE @Results TABLE
(
VehicleID int
Name varchar(100)
Locations varchar(4000)
)
DECLARE VehiclesCursor CURSOR FOR
SELECT
[VehicleID]
, [Name]
FROM [Vehicles]
OPEN VehiclesCursor
FETCH NEXT FROM VehiclesCursor INTO
@VehicleID
, @VehicleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Locations = ''
DECLARE LocationsCursor CURSOR FOR
SELECT
[City]
FROM [Locations]
WHERE [VehicleID] = @VehicleID
OPEN LocationsCursor
FETCH NEXT FROM LocationsCursor INTO
@LocationCity
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Locations = @Locations + @LocationCity
FETCH NEXT FROM LocationsCursor INTO
@LocationCity
END
CLOSE LocationsCursor
DEALLOCATE LocationsCursor
INSERT INTO @Results (VehicleID, Name, Locations) SELECT @VehicleID, @Name, @Locations
END
CLOSE VehiclesCursor
DEALLOCATE VehiclesCursor
SELECT * FROM @Results
但是,如您所见,这需要大量代码.我想要的是一个通用函数,它可以让我做这样的事情:
However, as you can see, this requires a great deal of code. What I would like is a generic function that would allow me to do something like this:
SELECT VehicleID
, Name
, JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles
这可能吗?或者类似的东西?
Is this possible? Or something similar?
推荐答案
如果您使用的是 SQL Server 2005,则可以使用 FOR XML PATH 命令.
If you're using SQL Server 2005, you could use the FOR XML PATH command.
SELECT [VehicleID]
, [Name]
, (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX))
FROM [Location]
WHERE (VehicleID = Vehicle.VehicleID)
FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM [Vehicle]
这比使用游标要容易得多,而且似乎工作得相当好.
It's a lot easier than using a cursor, and seems to work fairly well.
这篇关于如何创建 SQL Server 函数来“加入"从子查询到单个分隔字段的多行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!