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

我有两张桌子:

TableA
------
ID,
Name


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

这种关系是 TableA的一行-TableB的许多行。

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

ID     Name      SomeColumn


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

这样不行(子查询中有多个结果) :

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

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

注意，我不能简单地执行 GROUPBY 或类似的操作，因为它将返回 TableA行的多个结果。

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

150840 次浏览
小开

I think you are on the right track with COALESCE. See here for an example of building a comma-delimited string:

http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

小开

You may need to provide some more details for a more precise response.

Since your dataset seems kind of narrow, you might consider just using a row per result and performing the post-processing at the client.

So if you are really looking to make the server do the work return a result set like 

ID       Name       SomeColumn
1        ABC        X
1        ABC        Y
1        ABC        Z
2        MNO        R
2        MNO        S

which of course is a simple INNER JOIN on ID

Once you have the resultset back at the client, maintain a variable called CurrentName and use that as a trigger when to stop collecting SomeColumn into the useful thing you want it to do.

小开

In MySQL there is a group_concat function that will return what you're asking for.

SELECT TableA.ID, TableA.Name, group_concat(TableB.SomeColumn)
as SomColumnGroup FROM TableA LEFT JOIN TableB ON
TableB.TableA_ID = TableA.ID
小开

Assuming you only have WHERE clauses on table A create a stored procedure thus:

SELECT Id, Name From tableA WHERE ...


SELECT tableA.Id AS ParentId, Somecolumn
FROM tableA INNER JOIN tableB on TableA.Id = TableB.F_Id
WHERE ...

Then fill a DataSet ds with it. Then

ds.Relations.Add("foo", ds.Tables[0].Columns("Id"), ds.Tables[1].Columns("ParentId"));

Finally you can add a repeater in the page that puts the commas for every line

 <asp:DataList ID="Subcategories" DataKeyField="ParentCatId"
DataSource='<%# Container.DataItem.CreateChildView("foo") %>' RepeatColumns="1"
RepeatDirection="Horizontal" ItemStyle-HorizontalAlign="left" ItemStyle-VerticalAlign="top"
runat="server" >

In this way you will do it client side but with only one query, passing minimal data between database and frontend

小开
最佳答案

1. Create the 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. Use in subquery:

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

3. If you are using stored procedure you can do like this:

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
小开

Solution below:

SELECT GROUP_CONCAT(field_attr_best_weekday_value)as RAVI
FROM content_field_attr_best_weekday LEFT JOIN content_type_attraction
on content_field_attr_best_weekday.nid = content_type_attraction.nid
GROUP BY content_field_attr_best_weekday.nid

Use this, you also can change the Joins

小开

Even this will serve the purpose

Sample data

declare @t table(id int, name varchar(20),somecolumn varchar(MAX))
insert into @t
select 1,'ABC','X' union all
select 1,'ABC','Y' union all
select 1,'ABC','Z' union all
select 2,'MNO','R' union all
select 2,'MNO','S'

Query:

SELECT ID,Name,
STUFF((SELECT ',' + CAST(T2.SomeColumn AS VARCHAR(MAX))
FROM @T T2 WHERE T1.id = T2.id AND T1.name = T2.name
FOR XML PATH('')),1,1,'') SOMECOLUMN
FROM @T T1
GROUP BY id,Name

Output:

ID  Name    SomeColumn
1   ABC     X,Y,Z
2   MNO     R,S
小开

I have reviewed all the answers. I think in database insertion should be like:

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

The comma should be at previous end and do searching by like %,X,%

小开 
SELECT t.ID,
t.NAME,
(SELECT t1.SOMECOLUMN
FROM   TABLEB t1
WHERE  t1.F_ID = T.TABLEA.ID)
FROM   TABLEA t;

This will work for selecting from different table using sub query.

小开

I tried the solution priyanka.sarkar mentioned and the didn't quite get it working as the OP asked. Here's the solution I ended up with:

SELECT ID,
SUBSTRING((
SELECT ',' + T2.SomeColumn
FROM  @T T2
WHERE WHERE T1.id = T2.id
FOR XML PATH('')), 2, 1000000)
FROM @T T1
GROUP BY ID

提交答案