SQL multiple self joins to columns

Question

i have the following simplified information in a table called suites which unfortunately i cannot control

id  title            parentsuiteid
1   test             NULL
4   pay1             3
5   pay2             3
3   Bill Payments    2
14  Vouchers         2
15  Mini             2
2   Site Tests       NULL

I would like to have a horizontal representation to use for reporting such as

test
Site tests   Bill Payments  pay1
Site tests   Bill Payments  pay2
Site tests   Vouchers   
Site tests   Mini   

i was testing with the join

select a.id,a.title,b.title,a.parentsuiteid from #temp2 a
left outer join #temp2 b
on a.id = b.parentsuiteid

id  title           title       parentsuiteid
1   test            NULL            NULL
4   pay1            NULL            3
5   pay2            NULL            3
3   Bill Payments   pay1            2
3   Bill Payments   pay2            2
14  Vouchers        NULL            2
15  Mini            NULL            2
2   Site Tests      Bill Payments   NULL
2   Site Tests      Vouchers        NULL
2   Site Tests      Mini            NULL 

This works with two levels fine but i cannot predict how many levels in future there will be and it seems to get complicated with more than two

How do i get my output to look like the horizontal representation for +- 5 levels ?

Answer 1

Try this:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE temp
(
  ID int,
  title VARCHAR(50),
  parentsuiteid int null
)

INSERT INTO temp
VALUES
(1, 'test', NULL),
(4, 'pay1',3),
(5, 'pay2', 3),
(3, 'Bill Payments', 2),
(14, 'Vouchers', 2),
(15, 'Mini', 2),
(2, 'Site Tests', NULL)

Query 1:

;WITH recurs
AS
(
    SELECT ID, title, parentsuiteid, 0 as level
    FROM Temp
    WHERE parentsuiteid IS NULL
    UNION ALL
    SELECT t1.ID, CAST(t2.title + ' ' + t1.title as VARCHAR(50)), t1.parentsuiteid, t2.level + 1 
    FROM temp t1
    INNER JOIN recurs t2
        ON t1.parentsuiteid = t2.ID
)
SELECT title
FROM Recurs r1
WHERE NOT EXISTS (SELECT * from recurs r2 WHERE r2.parentsuiteid = r1.Id )

Results:

|                         TITLE |
|-------------------------------|
|                          test |
|           Site Tests Vouchers |
|               Site Tests Mini |
| Site Tests Bill Payments pay1 |
| Site Tests Bill Payments pay2 |

Answer 2

You can use Recursive CTE's :

WITH EmpsCTE
AS (
 SELECT empid
    ,mgrid
    ,firstname
    ,lastname
    ,0 AS distance
FROM HR.Employees
WHERE empid = 9

UNION ALL

SELECT M.empid
    ,M.mgrid
    ,M.firstname
    ,M.lastname
    ,S.distance + 1 AS distance
FROM EmpsCTE AS S
INNER JOIN HR.Employees AS M ON S.mgrid = M.empid
)
SELECT empid
,mgrid
,firstname
,lastname
,distance
FROM EmpsCTE;

empid       mgrid       firstname  lastname             distance 
----------- ----------- ---------- -------------------- ----------- 
9           5           Zoya       Dolgopyatova         0 
5           2           Sven       Buck                 1 
2           1           Don        Funk                 2 
1           NULL        Sara       Davis                3