-- TASK: 
--      to report parent name and all kids name concatenated in one row
--
--                    built by Gjuro Kladaric 2011-11-23
--
--           everybody is free to use this for whatever purpose, 
--                  but I do not accept any responsibility
--
-- NOTE:
--   of course, there might be (and usually there are) more than one person with the same name
--   in such a case you will have to adapt this query to use some unique person identifier 
--   instead of parent_name to have kids related to their proper parents
--
-- CREATE TABLE parents_and_kids
-- ( parent_name VARCHAR(50),
--   kid_name VARCHAR(50)
-- )


-- INSERT INTO parents_and_kids VALUES 
-- ('John Brown',   'Steve Brown'),
-- ('John Brown',   'Anne Brown'),
-- ('John Brown',   'Veronica Brown'),
-- ('Joseph Green', 'Maria Green'),
-- ('Joseph Green', 'Antonio Green'),
-- ('Joanna Blue',  ''),
-- ('Nina White',   'Mathias White'),
-- ('Nina White',   'Emma White'),
-- ('Annie Black',  'Nicolas Black')


SELECT  parent_name, 
        -- remove the excess comma + blank at the end of the string
        -- why -1 instead of -2?  because LEN() ignores trailing blanks :-)
        LEFT(kid_names_catenated,LEN(kid_names_catenated)-1)  'kid_names_catenated' 
FROM 
    (
        SELECT parent_name,
            (
                -- use RTRIM(pk1.kid_name) if kid_name is of type CHAR instead of VARCHAR
                SELECT pk1.kid_name + ', ' 
                FROM parents_and_kids pk1 
                WHERE pk1.parent_name = pk2.parent_name
                ORDER BY kid_name 
                -- FOR XML PATH does all the magic of concatenating all the rows data into one string
                FOR XML PATH('')
            ) kid_names_catenated
        FROM parents_and_kids pk2
      --limit this request to one parent by uncommenting the following WHERE clause
      --WHERE parent_name = 'John Brown'
        GROUP BY parent_name
    ) dummy_table_name