Concat rows using parent ID-CodePudding

Is there a way to concat to rows if the other row has a reference to the parent ID?

Here's my example:

 ---- -------- ----------- 
| Id | Title  | ParentID  |
 ---- -------- ----------- 
|  1 | AAAAAA | NULL      |
|  2 | BBBBBB | NULL      |
|  3 | CCCCCC | 1         |
|  4 | DDDDDD | 2         |
 ---- -------- -----------

Expected output will be

 ---- -------------- 
| Id | Title        | 
 ---- -------------- 
|  1 | AAAAAACCCCCC |  
|  2 | BBBBBBDDDDDD |
 ---- --------------

CodePudding user response：

Use self join with id = parentID. From this result concatenate both the title columns.

For example:

select 
    A.id, A.title || B.title as title 
from 
    (select * from table) A 
inner join 
    (select * from table) B on A.id = B.parentID

CodePudding user response：

try this

create table test(id int, title varchar(10), ParentId Int);

insert into test (id, title, ParentId) Values (1, 'AAAAAA', null), (2, 'BBBBBB', null), (3, 'CCCCCC', 1), (4, 'DDDDDD', 2);

SELECT T1.Id, T1.title   ''   T2.title
FROM test T1
INNER JOIN test T2 ON T1.Id = T2.ParentId;

http://sqlfiddle.com/#!18/c051fc/4