update a database table field with comma separated list from join

sql, sql-server, sql-server-2008-r2, sql-update

Solution

With the help of `FOR XML PATH` and STUFF to CONCATENATE the values, you can easily update the table `District` with your desired result.

UPDATE  a
SET     a.Schools = b.SchoolList
FROM    Districts a
        INNER JOIN
        (
            SELECT  DistrictId,
                    STUFF((SELECT ', ' + SchoolName
                            FROM Schools
                            WHERE DistrictId = a.DistrictId
                            FOR XML PATH (''))
                        , 1, 1, '')  AS SchoolList
            FROM    Districts AS a
            GROUP   BY DistrictId
        ) b ON A.DistrictId = b.DistrictId
WHERE   b.SchoolList IS NOT NULL

- SQLFiddle Demo

Problem

I have two tables named `Districts` and `Schools`. The `Districts` table contains a column named `Schools`. I need to populate the `Schools` column of the `Districts` table from the corresponding `Schools` table, so that each row in the `Districts` table has a comma separated list of values of school names from the `Schools` table. How can I do this? Should I use an `UPDATE` query or a stored procedure? I only got as far as: SQL Fiddle `Districts Table` ``` +------------+------+---------+ | DistrictId | Name | Schools | +------------+------+---------+ | 1 | a | | | 2 | b | | | 3 | c | | | 4 | d | | +------------+------+---------+ ``` `Schools Table` ``` +----------+------------+------------+ | SchoolId | SchoolName | DistrictId | +----------+------------+------------+ | 1 | s1 | 1 | | 2 | s2 | 1 | | 3 | s3 | 2 | | 4 | s4 | 2 | | 5 | s5 | 4 | +----------+------------+------------+ ``` How the Output Needs to be ``` +------------+------+---------+ | DistrictId | Name | Schools | +------------+------+---------+ | 1 | a | s1,s2 | | 2 | b | s3,s4 | | 3 | c | | | 4 | d | s5 | +------------+------+---------+ ```

Original source