SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE T(DISK_VOLUME varchar(100));



INSERT INTO T VALUES ('P:    220825387008 959589646336 DADOS');
INSERT INTO T VALUES ('I:    166207356928 959589646336 INDEXS');
INSERT INTO T VALUES ('E:    636080054272 799165902848 LOG');
INSERT INTO T VALUES ('C:    462246113280 999651536896');
4 rows affected

CREATE FUNCTION fn_split 
( @Words nvarchar(MAX)
, @splitStr varchar(50) 
)
RETURNS @Result_Table TABLE
	   (
		 [word] nvarchar(max) NULL
	   )
BEGIN 
    Declare @TempStr nvarchar(MAX)
    
    WHILE (CHARINDEX(@splitStr,@Words)>0)
	BEGIN
		Set @TempStr=SUBSTRING(@Words,1,CHARINDEX(@splitStr,@Words)-1)
		Insert into @Result_Table (word) Values (@TempStr)
		
		Set @Words = REPLACE(@Words,@TempStr+@splitStr,'')
	END/*End While*/
	
	IF(LEN(RTRIM(LTRIM(@Words)))>0 And CHARINDEX(@splitStr,RTRIM(LTRIM(@Words)))=0) 
	Begin
	    Set @TempStr=@Words 
	    
		Insert into @Result_Table (word) Values (@TempStr)
	    
	End 

   RETURN 
END
;WITH CTE as(
    SELECT val,word,ROW_NUMBER() OVER(PARTITION BY val order by val) rn
    FROM (
        SELECT replace(replace(replace(DISK_VOLUME,' ','*&'),'&*',''),'*&',' ') val
        FROM T
    ) t1 CROSS APPLY fn_split(t1.val,' ') v
)

SELECT max(CASE WHEN rn = 1 THEN word END) 'Caption',
       max(CASE WHEN rn = 2 THEN word END) 'Space1',
       max(CASE WHEN rn = 3 THEN word END) 'Space 2',
       max(CASE WHEN rn = 4 THEN word END) 'Volume Name '
FROM CTE
GROUP BY val


Caption
Space1
Space 2
Volume Name


C:
462246113280
999651536896
null

E:
636080054272
799165902848
LOG

I:
166207356928
959589646336
INDEXS

P:
220825387008
959589646336
DADOS


Warning: Null value is eliminated by an aggregate or other SET operation.

Caption	Space1	Space 2	Volume Name
C:	462246113280	999651536896	null
E:	636080054272	799165902848	LOG
I:	166207356928	959589646336	INDEXS
P:	220825387008	959589646336	DADOS