主页 / dba / 问题 / 219735
Accepted
Gerrit
Asked: 2018-10-11 00:13:11 +0800 CST

string_split 分成多列

  • 772

抱歉，如果这个问题非常基本，但我无法弄清楚，也找不到任何好的答案。

我有一个很长的文件、文件夹和大小列表。我需要将文件夹分成列，每个文件夹都有一个列。

我有一个 FilePath 作为字符串（例如 folder1\folder2\folder3）。我想把它分成多个列：

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

使用 cross apply string_split(PATH,'\') as 文件夹我得到 Folder1。使用 row_Number() 我可以定义列中的文件夹，但它始终只有 1 列。

实际示例：

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


Parentpath                      || Size   ||  Value
Business\Packets\Data\Archive   || 29334  || Business
sql-server t-sql

2 个回答

  1. Best Answer

    这也不漂亮，但它允许 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;

    使用此示例数据，我得到以下结果：

    在此处输入图像描述

    • 1

  2. 找到了一个解决方案，虽然我不确定，这是最好的方法，但可以肯定的是，这不是最漂亮的方法。

    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

    给我： 在此处输入图像描述

    我省略了大小，因为我有兴趣先将路径分成单独的文件夹。

    编辑：对不起，由于对结果进行排序，此屏幕截图与问题不同。澄清一下：显示结果的 ParentPath 将是：“Business Intelligence\GLS Paketdaten \ GLS Daten Archiv”

    或作为带有已发布内容的表格：

    MainFolder      |  SubFolder   |  secondSubfolder
Business        |   Packets    |      Data

    PS对不起，英语不是我的第一语言。我希望我写的可以理解。

    • 0

相关问题