Trust me!! MS SQL easy solution
SELECT ID as id,\ncase\nWHEN P_ID IS NULL THEN \'Root\'\nWHEN id IN (SELECT p_id FROM Tree) THEN \'Inner\'\nELSE \'Leaf\'\nend AS type FROM TREE
SELECT ID as id,\ncase\nWHEN P_ID IS NULL THEN \'Root\'\nWHEN id IN (SELECT p_id FROM Tree) THEN \'Inner\'\nELSE \'Leaf\'\nend AS type FROM TREE
[]