Prakhar Saxena Prakhar Saxena - 1 month ago 13
SQL Question

Printing strings next to each output row in sql?

I have been looking for an answer to this question. I have a table called "Tree" that contains pid of the node and another column that contains parent pid (ppid). And the table contains:

Pid Ppid
1 2
3 2
5 6
7 6
2 4
6 4
4 15
8 9
10 9
12 13
14 13
9 11
13 11
11 15
15 NULL


The aim is to print a message next to the Pid column stating the type of node, ie "Leaf", "Inner" or "Root", in a single query using Oracle/sql/sql server/etc.
The output is supposed to be something like this:

1 Leaf
2 Inner
3 Leaf
4 Inner
5 Leaf
6 Inner
7 Leaf
8 Leaf
9 Inner
10 Leaf
11 Inner
12 Leaf
13 Inner
14 Leaf
15 Root


I just need to know the way of printing the new column values. Can someone please help me?
Thank you in advance :)

Answer

In SQL SERVER & Oracle it can achieved using Recursive CTE

This is for SQL SERVER

;WITH data
     AS (SELECT *
         FROM   (VALUES (1,2 ),
                        (3,2 ),
                        (5,6 ),
                        (7,6 ),
                        (2,4 ),
                        (6,4 ),
                        (4,15 ),
                        (8,9 ),
                        (10,9 ),
                        (12,13 ),
                        (14,13 ),
                        (9,11 ),
                        (13,11 ),
                        (11,15 ),
                        (15,NULL ) ) tc (Pid, Ppid)),
     rec_cte
     AS (SELECT *
         FROM   data
         UNION ALL
         SELECT d.*
         FROM   rec_cte rc
                JOIN data d
                  ON rc.Ppid = d.Pid)
--select * from rec_cte
    SELECT Pid,
           CASE Count(Ppid)
             WHEN 0 THEN 'root'
             WHEN 1 THEN 'leaf'
             ELSE 'inner'
           END level
    FROM   rec_cte
    GROUP  BY Pid 

Result :

╔═════╦═══════╗
║ Pid ║ level ║
╠═════╬═══════╣
║   1 ║ leaf  ║
║   2 ║ inner ║
║   3 ║ leaf  ║
║   4 ║ inner ║
║   5 ║ leaf  ║
║   6 ║ inner ║
║   7 ║ leaf  ║
║   8 ║ leaf  ║
║   9 ║ inner ║
║  10 ║ leaf  ║
║  11 ║ inner ║
║  12 ║ leaf  ║
║  13 ║ inner ║
║  14 ║ leaf  ║
║  15 ║ root  ║
╚═════╩═══════╝