Subquery result into one row

Question

for example I have two table like this :

Table Parent
+===========+==============+
| FirstName | (PK)LastName |
+===========+==============+
| Bob       | Newbie       |
| Bella     | Goth         |
+===========+==============+

Table Child
+===========+==============+
| FirstName | (FK)LastName |
+===========+==============+
| Cassandra | Goth         |
| Cindy     | Newbie       |
| Mortimer  | Goth         |
+===========+==============+

what should I do to have a SELECT result like this :

+========+============+=====================+
| Family | ParentName | ChildName           |
+========+============+=====================+
| Goth   | Bella      | Cassandra, Mortimer |
| Newbie | Bob        | Cindy               |
+========+============+=====================+

what I've tried so far

SELECT
    A.LastName Family,
    A.FirstName ParentName,
    (
        SELECT
            COALESCE(B.FirstName + ',' , '') + B.FirstName
        FROM
            Child B
        WHERE
            B.LastName = A.LastName
    ) ChildName
FROM
    Parent A
ORDER BY
    Family ASC

but when I execute it shows

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

any idea?

score 2 · Accepted Answer · answered Dec 15 '15 at 16:26

2

The standard way here is to use some XML trick:

select *, STUFF((SELECT ',' + FirstName 
                 FROM child where LastName = p.LastName 
                 FOR XML PATH('')), 1, 1, '')
from parent p

answered Dec 15 '15 at 16:26

Giorgi Nakeuri

35,155
8
47
75

**LIFE SAVER**, could you explain why the first character of commas is deleted? – Rizky Dec 15 '15 at 16:37
1

@rizkynggakool, stuff function replaces characters from position 1 of length 1 with empty string. – Giorgi Nakeuri Dec 15 '15 at 18:26

Roman Marusyk · Answer 2 · 2015-12-15T16:21:02.697

1

You do not need to use subquery in this case, just a inner join. Try to use something like that:

SELECT
    A.LastName Family,
    A.FirstName ParentName,
    COALESCE(B.FirstName + ',' , '') + B.FirstName    ChildName
FROM
    Parent A
    INNER JOIN Child B on A.LastName = B.LastName
ORDER BY
    A.Family ASC

edited Dec 15 '15 at 16:21

answered Dec 15 '15 at 16:19

Roman Marusyk

23,328
24
73
116

This will not scale if someone has 3 or more children? – Giorgi Nakeuri Dec 15 '15 at 16:20

Subquery result into one row

2 Answers2