正如其他人所说，这可能不是***的做事方式，如果你解释你将用结果做什么，它可能会对我们有所帮助提供更好的选择

As others have said, this probably isn't the best way to do things, if you explain what you'll be doing with the results it might help us provide a better option

[另外，由于某些原因，下面代码的颜色显示很奇怪，所以将其复制并粘贴到您的 Sql 服务器中以更好地查看它]

[Also, for some reason the colours of the code below are showing up odd, so copy and paste it into your Sql server to see it better]

drop table #Path

create table #Path (item bigint,location varchar(1000))

insert into #Path 
select 16  ,'W:\2-Work\ALBO\00_Proposal\ALxO_Amendement #1_20091022_signed.pdf' union
select 17  ,'W:\2-Work\ALBO\00_Proposal\Level1\ALBO_Amendment #1_20110418.docx' union
select 18  ,'W:\2-Work\ALBO\00_Proposal\A\BR\T\X_#1_20110418_final.docx' union
select 19  ,'W:\2-Work\ALBO\MyOptionl\AO_Amendment_2 August 2013.docx'


select * from #Path;


with Path_Expanded(item,subitem,location, start, ending, split)
as(
select item
     , 1 --subitem begins at 1
     , location -- full location path
     , 0 --start searching the file from the 0 position
     , charindex('\',location) -- find the 1st '\' charactor
     , substring(location,0,charindex('\',location)) --return the string from the start position, 0, to the 1st '\' charactor

from #Path
union all
select item
     , subitem+1 --add 1 to subitem
     , location -- full location path
     , ending+1 -- start searching the file from the position after the last '\' charactor
     , charindex('\',location,ending+1)-- find the 1st '\' charactor that occurs after the last '\' charactor found
     , case when charindex('\',location,ending+1) = 0 then substring(location,ending+1,1000) --if you cant find anymore '\', return everything else after the last '\'
            else substring(location,ending+1, case when charindex('\',location,ending+1)-(ending+1) <= 0 then 0 
            else charindex('\',location,ending+1)-(ending+1) end )--returns the string between the last '\' charactor and the next '\' charactor
            end 

from Path_Expanded
where ending > 0 --stop once you can't find anymore '\' charactors
)


--pivots the results 
select item
    , max(case when subitem = 1 then split else '' end) as col1
    , max(case when subitem = 2 then split else '' end) as col2
    , max(case when subitem = 3 then split else '' end) as col3
    , max(case when subitem = 4 then split else '' end) as col4
    , max(case when subitem = 5 then split else '' end) as col5
    , max(case when subitem = 6 then split else '' end) as col6
    , max(case when subitem = 7 then split else '' end) as col7
    , max(case when subitem = 8 then split else '' end) as col8
    , max(case when subitem = 9 then split else '' end) as col9
    , max(case when subitem = 10 then split else '' end) as col10

from Path_Expanded
group by item

您可能更喜欢将每个文件夹放在自己的行上，如果是这样，请用下面的查询替换上面的数据透视部分

you might prefer to have each folder on its own row, if so replace the pivot part above with the below query instead

select  item
      , subitem
      , location
      , split from Path_Expanded where item = 16