Combine multiple results in a subquery into a single comma-separated value

Question

Hi,

I'm sorry if the question is long-winded and/or unclear. I will try and make it clearer with any feedback I get.

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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

Figured it out finally! Ben, please feel free to either edit this into your answer, or to create a new answer with this content. I will then mark your answer as the solution.

1. Create the UDF:

    CREATE FUNCTION CombineValues
    (
    @F_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

Answer 6

Typo in your query.

WHERE C.FK_ID = @FK_ID;

SB

WHERE C.FK_ID = @F_ID;

:)

Answer 7

Here the bellow Solution

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, also you can change the Joins

Thanks [email protected]

Answer 8

Even this will server the purpose

Sample data

declare @t table(id int, name varchar(20),somecolumn varchar(10))
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(10))
     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