I spend some time to solve this but no luck as im not so familiar on which aggregate functions and pivot approach i should use to get the output below. Raw Table is something like this:
ID | PARENT_ID | VALUES
----------------------------------
1 | NULL | PARENT A
2 | 1 | CHILD A
3 | 1 | CHILD A
4 | NULL | PARENT B
5 | 4 | CHILD B
6 | 4 | CHILD B
7 | 4 | CHILD B
8 | 4 | CHILD B
9 | 4 | CHILD B
10 | 4 | CHILD B
11 | 4 | CHILD B
126 | null | PARENT C
34 | 126 | CHILD C
12 | 126 | CHILD C
13 | 126 | CHILD C
I tried using this code but it seems it is not applicable to what I needed exactly :
Select
col1,
col,
trunc((rank() over (order by col)-1)/5) as d,
mod(rank() over (order by col)-1,5) as m
from
(
select col1, col2 as col from table1
union
select col1, col3 as col from table1
)
;
I think this can be done via pivot but i am not that familiar on how to approach it to be like this result table below. Should spread out the row dynamically into columns up to 6 cols.
I have Little to zero knowledge on table manipulations like this. Thanks in advance!!
PARENTS | CH1 | CH2 | CH3 | CH4 | CH5 | CH6
--------------------------------------------------------------------------
PARENT A | CHILD A1 | CHILD A1 | NULL | NULL | NULL | NULL
PARENT B | CHILD B | CHILD B | CHILD B |CHILD B | CHILD B | CHILD B
| CHILD B | NULL | NULL |NULL | NULL | NULL
PARENT C | CHILD C | CHILD C | CHILD C |NULL | NULL | NULL
CodePudding user response：
You can use:
SELECT parent,
ch1,
ch2,
ch3,
ch4,
ch5,
ch6
FROM (
SELECT CONNECT_BY_ROOT id AS parent_id,
CONNECT_BY_ROOT value AS parent,
value,
MOD(
ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT id ORDER BY id) - 1,
6
) 1 AS child_num,
TRUNC(
(ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT id ORDER BY id) - 1)
/ 6
) AS rn
FROM table_name t
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
)
PIVOT (
MAX(value) FOR child_num IN (
1 AS CH1,
2 AS CH2,
3 AS CH3,
4 AS CH4,
5 AS CH5,
6 AS CH6
)
)
Which, for the sample data:
CREATE TABLE table_name (ID, PARENT_ID, VALUE) AS
SELECT 1, NULL, 'PARENT A' FROM DUAL UNION ALL
SELECT 2, 1, 'CHILD A' FROM DUAL UNION ALL
SELECT 3, 1, 'CHILD A' FROM DUAL UNION ALL
SELECT 4, NULL, 'PARENT B' FROM DUAL UNION ALL
SELECT 5, 4, 'CHILD B' FROM DUAL UNION ALL
SELECT 6, 4, 'CHILD B' FROM DUAL UNION ALL
SELECT 7, 4, 'CHILD B' FROM DUAL UNION ALL
SELECT 8, 4, 'CHILD B' FROM DUAL UNION ALL
SELECT 9, 4, 'CHILD B' FROM DUAL UNION ALL
SELECT 10, 4, 'CHILD B' FROM DUAL UNION ALL
SELECT 11, 4, 'CHILD B' FROM DUAL UNION ALL
SELECT 126, NULL, 'PARENT C' FROM DUAL UNION ALL
SELECT 34, 126, 'CHILD C' FROM DUAL UNION ALL
SELECT 12, 126, 'CHILD C' FROM DUAL UNION ALL
SELECT 13, 126, 'CHILD C' FROM DUAL;
Outputs:
PARENT CH1 CH2 CH3 CH4 CH5 CH6 PARENT A CHILD A CHILD A null null null null PARENT B CHILD B CHILD B CHILD B CHILD B CHILD B CHILD B PARENT B CHILD B null null null null null PARENT C CHILD C CHILD C CHILD C null null null
db<>fiddle here
CodePudding user response：
Using the sample data you had provided, you can use a PIVOT to build the table into the format that you are looking for.
WITH
raw_table (id, parent_id, vals)
AS
(SELECT 1, NULL, 'PARENT A' FROM DUAL
UNION ALL
SELECT 2, 1, 'CHILD A' FROM DUAL
UNION ALL
SELECT 3, 1, 'CHILD A' FROM DUAL
UNION ALL
SELECT 4, NULL, 'PARENT B' FROM DUAL
UNION ALL
SELECT 5, 4, 'CHILD B' FROM DUAL
UNION ALL
SELECT 6, 4, 'CHILD B' FROM DUAL
UNION ALL
SELECT 7, 4, 'CHILD B' FROM DUAL
UNION ALL
SELECT 8, 4, 'CHILD B' FROM DUAL
UNION ALL
SELECT 9, 4, 'CHILD B' FROM DUAL
UNION ALL
SELECT 10, 4, 'CHILD B' FROM DUAL
UNION ALL
SELECT 11, 4, 'CHILD B' FROM DUAL
UNION ALL
SELECT 126, NULL, 'PARENT C' FROM DUAL
UNION ALL
SELECT 34, 126, 'CHILD C' FROM DUAL
UNION ALL
SELECT 12, 126, 'CHILD C' FROM DUAL
UNION ALL
SELECT 13, 126, 'CHILD C' FROM DUAL)
SELECT *
FROM (SELECT t2.vals AS parent_name,
t1.vals AS child_name,
ROW_NUMBER () OVER (PARTITION BY t2.vals ORDER BY t1.vals) AS child_num
FROM raw_table t1 LEFT JOIN raw_table t2 ON (t1.parent_id = t2.id))
PIVOT (MIN (child_name) ch FOR child_num IN (1, 2, 3, 4, 5, 6, 7));
PARENT_NAME 1_CH 2_CH 3_CH 4_CH 5_CH 6_CH 7_CH
______________ ___________ ___________ ___________ __________ __________ __________ __________
PARENT A CHILD A CHILD A
PARENT B CHILD B CHILD B CHILD B CHILD B CHILD B CHILD B CHILD B
PARENT C CHILD C CHILD C CHILD C
PARENT A PARENT B PARENT C
CodePudding user response：
so happy that you shared your idea, played it around and combind EJ Egyed & MT0 's solutions (i cannot tagged your name's hyperlink, not sure if possible) and it seems getting all the records so far. I even double check to count the data childs manually and via script by parent_ids. Here is the final code, would love if you can add some points too.
SELECT *
FROM (SELECT T2.pname AS parent_name,
t1.pname AS child_name,
MOD(
ROW_NUMBER() OVER (PARTITION BY t2.pname ORDER BY t1.pname) - 1,
6
) 1 AS child_num,
TRUNC(
(ROW_NUMBER() OVER (PARTITION BY t2.pname ORDER BY t1.pname) - 1)
/ 6
) AS rn
FROM sourceTable t1 JOIN sourceTable t2 ON (t1.parent_id = t2.cmw_id) )
PIVOT (MIN (child_name) ch FOR child_num IN (1, 2, 3, 4, 5, 6));