Copy all matching rows into one cell in another table in SQL Server 2008

Question

What I'm trying to do is to find all matching IDs from two tables then combine multiple rows into one cell in the original table.

So I have these two tables. Table 1

+-----+-------+
| id  | BS_ID |
+-----+-------+
| 999 | 12345 |
| 977 | 12347 |
| 955 | 12349 |
| 933 | 12351 |
+-----+-------+

Table 2

+-----+-------+------------+
| id  | BS_ID | callstatus |
+-----+-------+------------+
| 999 | 12345 | noanswer   |
| 999 | 12345 | contacted  |
| 977 | 12347 | noanswer   |
| 955 | 12349 | noanswer   |
| 933 | 12351 | noanswer   |
| 933 | 12351 | contacted  |
+-----+-------+------------+

What I want to happen is find all matching rows in table 2 based on the id in table 1. Then copy the "callstatus" in all the matching rows and put it in one cell in table 1 like the one below:

+-----+-------+---------------------+
| id  | BS_ID |     callstatus      |
+-----+-------+---------------------+
| 999 | 12345 | noanswer, contacted |
| 977 | 12347 | noanswer            |
| 955 | 12349 | noanswer            |
| 933 | 12351 | noanswer, contacted |

I have so far figured out how to count the instances in table 2 but I am stumped on how to copy the "callstatus" into that one cell in table 1.

SELECT table1.*
, (SELECT COUNT(*) 
   FROM table2 
   WHERE table2.id = table1.id) AS TOT 
FROM table1

Your `WHERE` clause doesn't sound right. You need to mention `WHERE` what `IS NOT NULL` — Raj, Sep 16 '15 at 06:22
possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Felix Pamittan, Sep 16 '15 at 06:23

score 3 · Accepted Answer · answered Sep 16 '15 at 06:36

Here you are:

(But keep in mind: You have no sort order, so the callstatus could appear in random order. And - if you don't need this just for output - think about the possibility to put this in an XML column)

DECLARE @Table1 TABLE(id INT,BS_ID INT);
INSERT INTO @Table1 VALUES
 (999,12345)
,(977,12347)
,(955,12349)
,(933,12351);

DECLARE @Table2 TABLE(id INT,BS_ID INT,callstatus VARCHAR(100));
INSERT INTO @Table2 VALUES
 (999,12345,'noanswer')
,(999,12345,'contacted')
,(977,12347,'noanswer')
,(955,12349,'noanswer')
,(933,12351,'noanswer')
,(933,12351,'contacted');


SELECT DISTINCT tbl1.id
               ,tbl1.BS_ID
               ,STUFF(
                        (
                        SELECT ', ' + tbl2.callstatus
                        FROM @Table2 AS tbl2
                        WHERE tbl1.id  = tbl2.id AND tbl1.BS_ID=tbl2.BS_ID
                        FOR XML PATH('')
                        ), 1, 2, '')  AS StatusList
FROM @Table1 AS tbl1

Sateesh Pagolu · Answer 2 · 2015-09-16T06:41:08.897

SELECT t1.id,t1.id_bsid,substring(statussummary1,1,len(statussummary1)-1)
from table1 t1
       inner join table2 as t21 on t1.id=t21.id and t1.id_bsid=t21.id_bsid
cross apply
(
    SELECT statussummary + ','
    FROM table2 AS t2
    WHERE t2.id = t21.id and t2.id_bsid = t21.id_bsid
    FOR XML PATH('')
)grping(statussummary1)
group by t1.id,t1.id_bsid,statussummary1

Fiddle here: http://sqlfiddle.com/#!3/18fb53

score 0 · Answer 3 · answered Sep 16 '15 at 06:49

Try this

  ;WITH CTE AS
    (
    SELECT
     DetailsTable.id
    ,DetailsTable.BS_ID
    ,(SELECT
        Concated.CALLSTATUS+ ','    
     FROM
        Table2  Concated
    WHERE Concated.id = DetailsTable.id

     FOR XML PATH('')

    )  As callstatus
    FROM
    Table1
    INNER JOIN
    Table2 DetailsTable
    ON Table1.id = DetailsTable.id
    )
    SELECT
      id
     ,BS_ID
     ,SUBSTRING(callstatus,0,LEN(callstatus)) AS CallStatus
    FROM 
    CTE
    GROUP BY
    callstatus
    ,id
    ,BS_ID
    Order by
    id desc

Copy all matching rows into one cell in another table in SQL Server 2008

3 Answers3