sql - Roll directories up to parent -

is there way roll data looks this:

what looking this:

y:\data\fs02-v\aetna\etl | data, development files

so rows 2 , 3 being children of row 1 should roll parent , parent should show file types contained. working in sql server , code produces source table:

      select          [mcl].[category description] category         , [sf].[directory] directory         , convert(bigint, [sf].[length]) filesize          , (select max([id]) [dbo].[split](right([sf].[directory],len([sf].[directory])-1),'\')) [levelsfromroot]     [dbo].[fs02v_sourcefiles] [sf]     inner join [dbo].[extensions] [e]         on [sf].[extension] = [e].[extension]     inner join [dbo].[mastercategorylookup] [mcl]         on [mcl].categoryid = [e].category     order [sf].[directory] 

table def:

create table [dbo].[fs02v_sourcefiles](     [length] [float] null,     [directory] [nvarchar](255) null,     [extension] [nvarchar](255) null,     [type] [nvarchar](max) null ) on [primary] textimage_on [primary]   create table [dbo].[extensions](     [extension] [nvarchar](255) null,     [type] [nvarchar](max) null,     [category] [int] null ) on [primary] textimage_on [primary] 

split function:

alter function [dbo].[split] (     @rowdata nvarchar(max),     @spliton nvarchar(5) )  returns @rtnvalue table  (     id int identity(1,1),     data nvarchar(max) )   begin      declare @cnt int     set @cnt = 1      while (charindex(@spliton,@rowdata)>0)     begin         insert @rtnvalue (data)         select              data = ltrim(rtrim(substring(@rowdata,1,charindex(@spliton,@rowdata)-1)))          set @rowdata = substring(@rowdata,charindex(@spliton,@rowdata)+1,len(@rowdata))         set @cnt = @cnt + 1     end      insert @rtnvalue (data)     select data = ltrim(rtrim(@rowdata))      return end 

the path(directory) stored nvarchar , should driver roll parent. assume path needs split , function doing similar path levels. think easier in sql raw data @ end of day going visualizing data in tableau if knows if easier use sql before feed tableau or use tableau source data try well.

solution simple:

use abolve sql prepare directories , group them respective files , roll using tableau , calculated fields.