SQLクエリーツリー

--1.  
CREATE TABLE #T
(
 id INT,
 [name] VARCHAR(10),
 [for] INT,
 [left] INT,
 [right] INT
)
--2.     
INSERT INTO #T VALUES(1, '  ', 0, 3, 2)
INSERT INTO #T VALUES(4, '  ', 2, 8, 9)
INSERT INTO #T VALUES(5, '  ', 2, 13, 0)
INSERT INTO #T VALUES(2, '  ', 1, 5, 4)
INSERT INTO #T VALUES(3, '  ', 1, 6, 7)
INSERT INTO #T VALUES(6, '  ', 3, 10, 11)
INSERT INTO #T VALUES(7, '  ', 5, 0, 12)
INSERT INTO #T VALUES(8, '  ', 4, 0, 0)
INSERT INTO #T VALUES(9, '  ', 4, 0, 0)
INSERT INTO #T VALUES(10, '  ',  6, 0, 0)
INSERT INTO #T VALUES(11, '  ',  6, 0, 0)
INSERT INTO #T VALUES(12, '  ',  7, 0, 0) 
INSERT INTO #T VALUES(13, '  ',  5, 0, 0)

DECLARE @M INT 
DECLARE @W NVARCHAR(max)=''
DECLARE @S NVARCHAR(max)=''
DECLARE @Sql NVARCHAR(max)=''
--3.    
;
WITH CT
AS
(
SELECT *,1 AS i FROM #T WHERE [for]=0
UNION ALL
SELECT #T.id,#T.name,#T.[for],#T.[left],#T.[right],i+1 FROM CT INNER JOIN #T ON CT.[left]=#T.id
UNION ALL
SELECT #T.id,#T.name,#T.[for],#T.[left],#T.[right],i+1 FROM CT INNER JOIN #T ON CT.[right]=#T.id
)
SELECT @M=MAX(I) FROM CT
--4.    
;WITH CT
AS
(
 SELECT POWER(2,@M)-1 AS A
 UNION ALL
 SELECT A-1 FROM CT WHERE A>1
)
SELECT @W=@W+','+QUOTENAME(A),@S=@S+',isnull('+QUOTENAME(A)+','''') as '+QUOTENAME(A) FROM CT ORDER BY CT.A
OPTION(MAXRECURSION 32767)
SET @W=STUFF(@W,1,1,'')
--5.  +     
SET @Sql='
WITH CT
AS
(
SELECT *,1 AS i, POWER(2,@M)/2 AS j FROM #T WHERE [for]=0
UNION ALL
SELECT #T.id,#T.name,#T.[for],#T.[left],#T.[right],i+1,j-POWER(2,@M-i-1) FROM CT INNER JOIN #T ON CT.[left]=#T.id
UNION ALL
SELECT #T.id,#T.name,#T.[for],#T.[left],#T.[right],i+1,j+POWER(2,@M-i-1) FROM CT INNER JOIN #T ON CT.[right]=#T.id
)
SELECT i as id'+@S+' FROM  
(
SELECT * FROM 
(
SELECT name,i,j FROM CT 
) A
PIVOT
(
 MAX(name)
 FOR j IN
 ('+@W+') 
)P
)Q'
EXEC  sp_executesql @Sql,N'@M int',@M
DROP TABLE #T