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 folder1folder2folder3).
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.
select [Parentpath], [Size], [spl].[value] from Files cross apply string_split([ParentPath], '') as [spl] Parentpath || Size || Value BusinessPacketsDataArchive || 29334 || Business
This is also not pretty, but it allows for asmanysubfoldersasyoumayhave.
-- borrowing from Brent: CREATE TABLE #Files (Parentpath varchar(100), Size int); INSERT INTO #Files (Parentpath, Size) VALUES ('BusinessPacketsDataArchive', 29334), ('CoachLossWharfCurrentBlatSplungeMore', 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:
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
I left out the Size, as I was interested in dividing the Path into seperate folders first.
Sorry, this Screenshot is different to the question, due to sorting the results.
ParentPath to the displayed result would be:
“Business IntelligenceGLS 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.