CODEWITHSUNDEEP
sqlsql-servergroup-by
Ebikeneser
Ebikeneser

Reputation: 2364

Select all child entries and group by parent

I have the following data block -

+-----------+----------+
| parent_id | child_id |
+-----------+----------+
|         1 |       11 |
|         1 |       12 |
|         1 |       13 |
|         2 |       12 |
|         2 |       13 |
|         2 |       14 |
|         3 |       15 |
|         3 |       16 |
|         4 |       16 |
|         5 |       12 |
|         5 |       16 |
+-----------+----------+

I want to do a select statement where by I can find all child_id's that belong to parents and group the results, something like -

+-----------+----------+
| parent_id | child_id |
+-----------+----------+
|         1 | 12 & 13  |
|         2 |          |
+-----------+----------+ 
+-----------+----------+
| parent_id | child_id |
+-----------+----------+
|         1 |      12  |
|         2 |          |
|         5 |          |
+-----------+----------+
+-----------+----------+
| parent_id | child_id |
+-----------+----------+
|         3 |       16 |
|         4 |          |
|         5 |          |
+-----------+----------+

So parents 1 and 2 both have children 12 and 13. Parents 1,2 and 5 all have 12 as a child, and 3,4 and 5 all have 16 as a child.

Upvotes: 1

Views: 1720

Answers (2)

D-Shih
D-Shih

Reputation: 46219

You can try to use cte get a result set which contains your expect parent_id and child_id then use FOR XML PATH combine them with &.

finally use row_number window function create row number with CASE WHEN do condition aggregate function, let only first-row display child_id.

;with cte as (
    SELECT t1.*
    FROM T t1 
    where 
       EXISTS (SELECT 1 FROM T tt WHERE t1.child_id = tt.child_id AND t1.parent_id <> tt.parent_id)
    AND 
       t1.parent_id in (1,2) --add you want to get parent_id id
), cte1 as (
     SELECT 
      distinct parent_id,
      STUFF((
         select  '&' + CAST(tt.child_id AS VARCHAR(MAX))  
         from cte tt 
         where t1.parent_id = tt.parent_id 
        FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
      ,1,1,'') AS child_id
     FROM cte t1
) 
SELECT parent_id,(case when rn = 1 then child_id else '' end) child_id
FROM (
   select *,row_number() over(order by parent_id) rn
   from cte1
) t1

Sqlfiddle

Result

parent_id   child_id
1           12&13
2

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I think you want :

SELECT t.*
FROM table t 
WHERE EXISTS (SELECT 1 FROM table t1 WHERE t1.child_id = t.child_id AND t1.parent_id <> t.parent_id);

Upvotes: 2

Related Questions