SQL Server CTE - recursion -

i have 2 tables, first contains data of documents , second directories.

table 1

docid  dirid  name  order -----  -----  ----  ----- 1      4      doc1  2 2      1      doc2  1 3      5      doc3  1 4      3      doc4  1 5      4      doc5  1 

table 2

dirid  parentid  name -----  --------  ---- 1      null      root 2      1         dir1 3      2         dir2 4      1         dir3 5      3         dir4 

structure

root -dir1  -dir2   -dir4    -doc3    -doc4  -dir3  -doc5   -doc1  -doc2  

i'm trying create cte in t-sql, generate result, can't figure out how it. can suggest solution?

doc2 dir3/doc5 dir3/doc1 dir1/dir2/doc4 dir1/dir2/dir4/doc3 

root not shown, documents sorted order within directory , result sorted lowest depth ordered name of full path.

use cte generating folder hierarchy
join documents folders
, order result using created path purpose

declare @separator varchar(1) = '\\' --generating folder hierarchy ;with info (     select f.dirid     , f.parentid     , f.name     , cast(f.name varchar(255)) pathvalue     folders f     f.parentid null      union      select f.dirid     , f.parentid     , f.name     , cast(i.pathvalue + @separator + f.name varchar(255))     folders f     inner join info on f.parentid = i.dirid )  -- join documents folder hirarchy select i.parentid , i.dirid , i.name , d.docid , d.name , i.pathvalue + @separator + cast(d.ordernum varchar(255)) + '.' + d.name orderpath , i.pathvalue + @separator + d.name documentpath info inner join documents d on d.dirid = i.dirid  union  -- adding null row folder, show folder in result no documents -- can removed if want show folders containing documents select i.parentid , i.dirid , i.name , null , null , i.pathvalue orderpath , i.pathvalue documentpath info order orderpath 

sql fiddle