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
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 |