Dudu Markovitz Dudu Markovitz - 1 month ago 8
SQL Question

SQL Challenge/Puzzle: How to create an ASCII art hierarchy tree with an SQL query?

The initial motivation for this one, was to display Oracles' actual execution plans, saved in GV$SQL_PLAN, in a visual, clear way.





  • I've attached my suggested solutions.


    • Please feel free to add yours, as long as it fulfills the requirements.

    • Please mention the database name your solution was written for.







Requirements



Input


  • A table containing columns "id" (node's id) and "pid" (node's parent id).



Output


  • The result should be a an ASCII art graph (see example below)


    • Each pair of "id" and "pid" nodes should be connected with an edge.

    • The root node might have an additional single edge.

    • There should be no other edged especially not edges that are not connected to any node in one of their sides.




Code


  • A single SELECT statement based only on native SQL


    • No UDF (User Defined Functions).

    • No T-SQL, PL/SQL etc.




Sample data



create table h (id int,pid int);

insert into h (id,pid) values (0 ,null);
insert into h (id,pid) values (1 ,0 );
insert into h (id,pid) values (2 ,1 );
insert into h (id,pid) values (3 ,2 );
insert into h (id,pid) values (4 ,3 );
insert into h (id,pid) values (5 ,4 );
insert into h (id,pid) values (6 ,3 );
insert into h (id,pid) values (7 ,6 );
insert into h (id,pid) values (8 ,7 );
insert into h (id,pid) values (9 ,8 );
insert into h (id,pid) values (10 ,9 );
insert into h (id,pid) values (11 ,10 );
insert into h (id,pid) values (12 ,9 );
insert into h (id,pid) values (13 ,12 );
insert into h (id,pid) values (14 ,8 );
insert into h (id,pid) values (15 ,6 );
insert into h (id,pid) values (16 ,15 );
insert into h (id,pid) values (17 ,6 );
insert into h (id,pid) values (18 ,17 );
insert into h (id,pid) values (19 ,17 );
insert into h (id,pid) values (20 ,3 );
insert into h (id,pid) values (21 ,20 );
insert into h (id,pid) values (22 ,21 );
insert into h (id,pid) values (23 ,22 );
insert into h (id,pid) values (24 ,21 );


Results



Vertical siblings



|
|____ 1
|
|____ 2
|
|____ 3
|
|____ 4
| |
| |____ 5
|
|____ 6
| |
| |____ 7
| | |
| | |____ 8
| | |
| | |____ 9
| | | |
| | | |____ 10
| | | | |
| | | | |____ 11
| | | |
| | | |____ 12
| | | |
| | | |____ 13
| | |
| | |____ 14
| |
| |____ 15
| | |
| | |____ 16
| |
| |____ 17
| |
| |____ 18
| |
| |____ 19
|
|____ 20
|
|____ 21
|
|____ 22
| |
| |____ 23
|
|____ 24


Horizontal siblings



|
|
|
0
|
|
|
|
|
1
|
|
|
|
|
2
|
|
|
|
|
3
|
|
---------------------------------------
| | |
| | |
4 6 20
| | |
| | |
| ------------------- |
| | | | |
| | | | |
5 7 15 17 21
| | | |
| | | |
| | ------ ------
| | | | | |
| | | | | |
8 16 18 19 22 24
| |
| |
-------- |
| | |
| | |
9 14 23
|
|
------
| |
| |
10 12
| |
| |
| |
| |
| |
11 13

Answer

SQLite

Vertical siblings

with        last_sibling (id)
            as
            (
                select      max (id)
                from        h
                group by    pid
            )

           ,tree (id,branch,path)
            as
            (
                select      1       as id
                           ,''      as branch
                           ,'001'   as path

                union all

                select      h.id
                           ,t.branch || case when ls.id is not null then ' ' else '|' end || '    '
                           ,t.path || '_' || substr ('00000' || h.id,-5)

                from                    tree            t

                            left join   last_sibling    ls

                            on          ls.id   =
                                        t.id

                            join        h

                            on          h.pid =
                                        t.id
            )

           ,vertical_space (n)
            as
            (
                select      1

                union all

                select      vs.n + 1
                from        vertical_space  vs
                where       vs.n < 2
            )

select      t.branch || case vs.n when 1 then '|____' || ' ' || cast (t.id as text) else '|' end

from                    tree            t

            cross join  vertical_space  vs

order by    t.path
           ,vs.n desc
;