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

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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    lt asp DataList ID  Subcategories  DataKeyField  ParentCatId   DataSource   lt    Container DataItem CreateChildView  foo     gt   RepeatColumns  1   RepeatDirection  Horizontal  ItemStyle-HorizontalAlign  left  ItemStyle-VerticalAlign  top   runat  server   gt    In this way you will do it client side but with only one query  passing minimal data between database and frontend

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Combine multiple results in a subquery into a single comma-separated value

Examples related to sql-server

Examples related to tsql

Examples related to join