sqlsql-serversql-server-2008-r2

Using STUFF to get children value grouped by parent ID


I'm looking for a solution to get children's values in a single cell, I've found out about STUFF but when I'm trying to apply it to a self-referencing table it does not work.

Neither CONCAT nor CONCAT_WS work 'not a recognized built-in function name'.

Table:

ID ParentID Value
1 1 HELLO
2 1 Foo
3 1 Bar
4 3 Blob

My query at this point :

SELECT TaskParent.TaskID,
    ValueValues = STUFF((
            SELECT DISTINCT ' | ' + TaskChildren.Value
            FROM tblTask TaskChildren
                JOIN tblTask TaskParent2 ON TaskParent.TaskID = TaskChildren.TaskGroupID
            WHERE
                TaskParent2.TaskID = TaskParent.TaskID
                AND TaskParent2.TaskID not like TaskParent2.TaskParentID
            FOR XML PATH('')
            ), 1, 3, ''
        )
FROM tblTask Task
    JOIN tblTask TaskParent ON TaskParent.TaskID = Task.ParentID
GROUP BY TaskParent.TaskID

In the result from the query above I'm getting :

ParentID Value
1 Foo

But I want :

ParentID Value
1 Foo / Bar

I'm sorry if this was already answered, but I already spent too much time looking for it.

For more details when I add Task.ID to the query the result is the following :

ID ParentID Value
2 1 Foo
3 1 Bar

Here is modifications I made on @@siggemannen solution

select TaskParents.ID, substring((select ' | ' + Value from tblTask TaskValues where TaskValues.ParentID = TaskParents.ID for xml path('')), 4, 99999) as Children
from tblTask Task
    LEFT JOIN tblTask TaskParents ON TaskParents.ID = Task.ParentID
group by TaskParents.ID
ORDER BY TaskParents.ID

Solution

  • Something like this perhaps:

    select *
    into tblTask
    from (
        VALUES  (1, 1, N'HELLO')
        ,   (2, 1, N'Foo')
        ,   (3, 1, N'Bar')
        ,   (4, 3, N'Blob')
    ) t (ID,ParentID,Value)
    
    
    select ID, substring((select ' | ' + value from tbltask t2 where t2.parentid = t.id for xml path(''), type).value('.', 'nvarchar(max)'), 4, 99999) as Children
    from tblTask t
    group by ID
    

    Edit: Alternative method without TYPE:

    select ID, substring((select ' | ' + value from tbltask t2 where t2.parentid = t.id for xml path('')), 4, 99999) as Children
    from tblTask t
    group by ID
    

    This returns whatever children a parent has:

    ID Children
    1 HELLO | Foo | Bar
    2 NULL
    3 Blob
    4 NULL