string_split into multiple columns

Question

Sorry if this question is very basic, but I just can't figure it out, and couldn't find any good answers.

I have a very long list of files, folders and Sizes. I need to Seperate the folders to columns, that I have a column for each folder.

I have a FilePath as a String (eg folder1\folder2\folder3). I want to seperate this into multiple Columns:

 First  |  Second  |  Third  |  Fourth  |  ...
folder1 | folder2  | folder3 | NULL     | ...
Foldera | folde rb  | folderc | folderd  |

using cross apply string_split(PATH,'\')as folder I get Folder1. using row_Number() I can define wich folder I have in my column, but it is always only 1 column.

Actual Example:

select [Parentpath], [Size], [spl].[value] from Files
cross apply string_split([ParentPath], '\') as [spl]


Parentpath                      || Size   ||  Value
Business\Packets\Data\Archive   || 29334  || Business

Answer 1

This is also not pretty, but it allows for as\many\sub\folders\as\you\may\have.

-- borrowing from Brent:
CREATE TABLE #Files (Parentpath varchar(100), Size int);

INSERT INTO #Files (Parentpath, Size)
  VALUES ('Business\Packets\Data\Archive', 29334),
  ('Coach\Loss\Wharf\Current\Blat\Splunge\More', 7337);

DECLARE @s char(1) = CHAR(92), @sql nvarchar(max) = N'SELECT ParentPath, Size';

SELECT ParentPath, Size, value, rn = ROW_NUMBER() OVER 
  (PARTITION BY ParentPath ORDER BY CHARINDEX(value + @s, ParentPath + @s))
  INTO #x FROM #Files AS f CROSS APPLY STRING_SPLIT(f.ParentPath, @s) AS t;

DECLARE @max int = (SELECT MAX(rn) FROM #x);

SELECT @sql += N',  Folder' + RTRIM(rn) + N' = MAX(CASE WHEN rn = ' 
            +  RTRIM(rn) + N' THEN value END)' 
  FROM (SELECT rn = ROW_NUMBER() OVER (ORDER BY name) FROM sys.all_columns) AS x
  WHERE rn <= @max;

SET @sql += N' FROM #x GROUP BY ParentPath, Size ORDER BY Folder1' 
         +  CASE WHEN @max > 1 THEN N', Folder2' ELSE N'' END + N';';

EXEC sys.sp_executesql @sql;

DROP TABLE #x, #Files;

With this sample data I get the following results:

Answer 2

Found a Solution, although I am not sure, this is the best way to do it, but certainly sure, this is not the prettiest way to do it.

select x1.value as MainFolder, x2.value as SubFolder, x3.value as [secondSubFolder]
from Files f 
cross apply (
select xx.value, ROW_NUMBER() OVER (Partition by f.ParentPath order by f.ParentPath) AS PP
from string_split (f.ParentPath,'\') as xx
) as x1
cross apply (
select xx2.value, ROW_NUMBER() OVER (Partition by f.ParentPath order by f.ParentPath) as PP2
from string_split (f.ParentPath,'\') as xx2
) as x2
cross apply (
select xx3.value, ROW_NUMBER() OVER (Partition by f.ParentPath order by f.ParentPath) as PP3
from string_split (f.ParentPath,'\') as xx3
) as x3
where PP = 1
and PP2 = 2
and pp3 = 3
group by x1.value, x2.value, x3.value
order by MainFolder asc, SubFolder asc

is giving me :

I left out the Size, as I was interested in dividing the Path into seperate folders first.

Edit: Sorry, this Screenshot is different to the question, due to sorting the results. To clarify: ParentPath to the displayed result would be: "Business Intelligence\GLS Paketdaten \ GLS Daten Archiv"

Or as a Table with posted content:

MainFolder      |  SubFolder   |  secondSubfolder
Business        |   Packets    |      Data 

P.S Sorry, English is not my first language. I hope I wrote it understandable.