SQL trick for flattening a parent-child table and vice versa

Among other things this blog is about saving tricks for future use. This SQL trick I used in my previous example of hierarchical organization structure = How to flatten a parent-child table.

Suppose you have the following parent-child table for your hierarchy:

CREATE TABLE hierarchy_parent_child (
node_id INTEGER NOT NULL,
parent_id INTEGER,
node_name VARCHAR(40)
);

.. and the table contains data like this (please note NULL in root parent_id):



.. and you want to change it into flat format, here's the SQL:

SELECT
lev01.node_id id_01, lev01.node_name name_01,
lev02.node_id id_02, lev02.node_name name_02,
lev03.node_id id_03, lev03.node_name name_03,
lev04.node_id id_04, lev04.node_name name_04,
lev05.node_id id_05, lev05.node_name name_05,
lev06.node_id id_06, lev06.node_name name_06,
lev07.node_id id_07, lev07.node_name name_07,
lev08.node_id id_08, lev08.node_name name_08,
lev09.node_id id_09, lev09.node_name name_09,
lev10.node_id id_10, lev10.node_name name_10
FROM hierarchy_parent_child lev01
LEFT OUTER JOIN hierarchy_parent_child lev02 ON lev01.node_id = lev02.parent_id
LEFT OUTER JOIN hierarchy_parent_child lev03 ON lev02.node_id = lev03.parent_id
LEFT OUTER JOIN hierarchy_parent_child lev04 ON lev03.node_id = lev04.parent_id
LEFT OUTER JOIN hierarchy_parent_child lev05 ON lev04.node_id = lev05.parent_id
LEFT OUTER JOIN hierarchy_parent_child lev06 ON lev05.node_id = lev06.parent_id
LEFT OUTER JOIN hierarchy_parent_child lev07 ON lev06.node_id = lev07.parent_id
LEFT OUTER JOIN hierarchy_parent_child lev08 ON lev07.node_id = lev08.parent_id
LEFT OUTER JOIN hierarchy_parent_child lev09 ON lev08.node_id = lev09.parent_id
LEFT OUTER JOIN hierarchy_parent_child lev10 ON lev09.node_id = lev10.parent_id
WHERE lev01.parent_id IS NULL;

.. then it returns rows like this:



The example above works for ten levels but it's easy to add more levels by repeating the pattern.

If you want to go vice versa, suppose you have the following flat hierarchy table:

CREATE TABLE hierarchy_flat (
id_01 INTEGER,
name_01 VARCHAR(40),
id_02 INTEGER,
name_02 VARCHAR(40),
id_03 INTEGER,
name_03 VARCHAR(40),
id_04 INTEGER,
name_04 VARCHAR(40),
id_05 INTEGER,
name_05 VARCHAR(40),
id_06 INTEGER,
name_06 VARCHAR(40),
id_07 INTEGER,
name_07 VARCHAR(40),
id_08 INTEGER,
name_08 VARCHAR(40),
id_09 INTEGER,
name_09 VARCHAR(40),
id_10 INTEGER,
name_19 VARCHAR(40)
);

.. then you can do SQL like this:

SELECT DISTINCT id_01 node_id, NULL parent_id, name_01 node_name
FROM hierarchy_flat
UNION
SELECT DISTINCT id_02 node_id, id_01 parent_id, name_02 node_name
FROM hierarchy_flat WHERE id_02 IS NOT NULL
UNION
SELECT DISTINCT id_03 node_id, id_02 parent_id, name_03 node_name
FROM hierarchy_flat WHERE id_03 IS NOT NULL
UNION
SELECT DISTINCT id_04 node_id, id_03 parent_id, name_04 node_name
FROM hierarchy_flat WHERE id_04 IS NOT NULL
UNION
SELECT DISTINCT id_05 node_id, id_04 parent_id, name_05 node_name
FROM hierarchy_flat WHERE id_05 IS NOT NULL
UNION
SELECT DISTINCT id_06 node_id, id_05 parent_id, name_06 node_name
FROM hierarchy_flat WHERE id_06 IS NOT NULL
UNION
SELECT DISTINCT id_07 node_id, id_06 parent_id, name_07 node_name
FROM hierarchy_flat WHERE id_07 IS NOT NULL
UNION
SELECT DISTINCT id_08 node_id, id_07 parent_id, name_08 node_name
FROM hierarchy_flat WHERE id_08 IS NOT NULL
UNION
SELECT DISTINCT id_09 node_id, id_08 parent_id, name_09 node_name
FROM hierarchy_flat WHERE id_09 IS NOT NULL
UNION
SELECT DISTINCT id_10 node_id, id_09 parent_id, name_10 node_name
FROM hierarchy_flat WHERE id_10 IS NOT NULL
ORDER BY 1

.. and you get the original parent-child table.

Again, repeat the pattern to enable more than ten levels.