ms sql split string into columns

here is excellent example how to split string into columns in TSQL (microsoft SQL server)

  
Declare @products varchar(200) = 'one::two::tree' 
Declare @individual varchar(20) = null 
declare @res table(ident int identity(1,1),val varchar(20)) 
 
 
WHILE LEN(@products) > 0 
BEGIN 
    IF PATINDEX('%::%',@products) > 0 
    BEGIN 
        SET @individual = SUBSTRING(@products, 0, PATINDEX('%::%',@products)) 
        Insert into @res (val) values (@individual) 
        SET @products = SUBSTRING(@products, LEN(@individual + ';;') + 1, 
                                                     LEN(@products)) 
    END 
    ELSE 
    BEGIN 
        SET @individual = @products 
        SET @products = NULL 
        Insert into @res (val) values (@individual) 
    END 
END 
 
Select * from @res p 
Pivot( max(p.val) for p.ident in ([1],[2],[3]) ) as PIV