CODEWITHSUNDEEP
sqloracle-database
TJ Grant
TJ Grant

Reputation: 59

Retrieve Parents and Child Rows In Single Row

I have a lot of SQL experience but this one is beyond my understanding. I have a table such as:

ID    NAME    PARENT_ID
1     A       0
2     B       1
3     C       1
4     D       0
5     E       4
6     F       4
7     G       4

And I need to write a single SQL statement that will return all the parents and their children in a single row:

ID    PARENT    CHILD_1     CHILD_2     CHILD_3  ...  CHILD_N
1     A         B           C
4     D         E           F           G

I do not know how many children each parent has before hand - it is variable.

Is there anyway to do this in a single SQL statement?

Thanks.

Upvotes: 0

Views: 2412

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

You can use some of the very cool "dynamic pivot" PL/SQL solutions out there (which I don't recommend for production code -- they work 99% but fail for some odd-ball cases, in my experience).

Otherwise, you need to tell Oracle ahead of time which columns you expect your SQL to output. That means, you can only do what you're looking to do if you implement a hard cap on the maximum number of child columns you'll include.

If you can live with having to do that, then this should work. I took some guesses about how you would want it to work if your data had a hierarchy with multiple levels. (Take a look at row "H" in the sample data and think about how you would want that displayed.)

WITH d AS ( 
SELECT 1 id, 'A' name, 0 parent_id
FROM DUAL UNION ALL
SELECT 2, 'B', 1
FROM DUAL UNION ALL
SELECT 3, 'C', 1
FROM DUAL UNION ALL
SELECT 4, 'D', 0
FROM DUAL UNION ALL
SELECT 5, 'E', 4
FROM DUAL UNION ALL
SELECT 6, 'F', 4
FROM DUAL UNION ALL
SELECT 7, 'G', 4
FROM DUAL UNION ALL
SELECT 8, 'H', 7

FROM DUAL
),
h as (
select prior d.name parent, 
       level lvl,
       case when level = 1 then null else d.name end child_name, 
       case when level = 1 then null else  row_number() over ( partition by prior d.name, level order by d.name) end child_Number
from d
connect by parent_id = prior id
start with parent_id = 0 )
select * from h
pivot ( max(child_name) for (child_number) in (1 AS "CHILD_1",2 AS "CHILD_2",3 AS "CHILD_3",4 AS "CHILD_4",5 AS "CHILD_5") )
where lvl > 1
order by parent;

Upvotes: 1

Related Questions