将子查询中的多个结果合并为一个以逗号分隔的值

Combine multiple results in a subquery into a single comma-separated value(将子查询中的多个结果合并为一个以逗号分隔的值)

问题描述

我有两张桌子:

TableA
------
ID,
Name

TableB
------
ID,
SomeColumn,
TableA_ID (FK for TableA)

关系是一行TableA - 许多TableB.

The relationship is one row of TableA - many of TableB.

现在，我想看到这样的结果:

Now, I want to see a result like this:

ID     Name      SomeColumn

1.     ABC       X, Y, Z (these are three different rows)
2.     MNO       R, S

这不起作用(子查询中有多个结果):

This won't work (multiple results in a subquery):

SELECT ID,
       Name, 
       (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID)
FROM TableA

如果我在客户端进行处理，这是一个微不足道的问题.但这意味着我必须在每个页面上运行 X 次查询，其中 X 是 TableA 的结果数.

This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.

请注意，我不能简单地执行 GROUP BY 或类似操作，因为它会为 TableA 的行返回多个结果.

Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.

我不确定使用 COALESCE 或类似方法的 UDF 是否可行?

I'm not sure if a UDF, utilizing COALESCE or something similar might work?

推荐答案

1.创建 UDF:

CREATE FUNCTION CombineValues
(
    @FK_ID INT -- The foreign key from TableA which is used 
               -- to fetch corresponding records
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SomeColumnList VARCHAR(8000);

SELECT @SomeColumnList =
    COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20)) 
FROM TableB C
WHERE C.FK_ID = @FK_ID;

RETURN 
(
    SELECT @SomeColumnList
)
END

2.在子查询中使用:

SELECT ID, Name, dbo.CombineValues(FK_ID) FROM TableA

3.如果您使用的是存储过程，您可以这样做:

CREATE PROCEDURE GetCombinedValues
 @FK_ID int
As
BEGIN
DECLARE @SomeColumnList VARCHAR(800)
SELECT @SomeColumnList =
    COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20)) 
FROM TableB
WHERE FK_ID = @FK_ID 

Select *, @SomeColumnList as SelectedIds
    FROM 
        TableA
    WHERE 
        FK_ID = @FK_ID 
END

这篇关于将子查询中的多个结果合并为一个以逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！