Combine multiple results in a subquery into a sing

问题:

I\'ve got two tables:

TableA
------
ID,
Name

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

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

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.

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

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

回答1:

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

回答2:

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

回答3:

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

回答4:

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

回答5:

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.

回答6:

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

回答7:

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

回答8:

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

回答9:

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.

回答10:

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,%