string_split into multiple columns
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 &lt;= @max;

SET @sql += N' FROM #x GROUP BY ParentPath, Size ORDER BY Folder1' 
         +  CASE WHEN @max &gt; 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: