how to separate string into different columns?

Question

I have got a table with the entries like below:

    MachineName
-----------------------

    Ab bb zecos
    a zeng
    zeng
    empty

4 rows in a table.

How can I separate those 4 rows to get output like the following:

       M1       M2       M3
-----------------------------------
       Ab       bb      zecos
       a        zeng     NULL
       zeng     NULL     NULL
       NULL     NULL     NULL

Answer 1

Rather than using the split function, there is a function called ParseName which will return the specified part of the object which splits the string delimited by. Please go through this ParseName which will help you.

Declare @Sample Table
(MachineName varchar(max))

Insert into @Sample
values 
('Ab bb zecos'),('a Zeng')


  SELECT 
  Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 1)) As [M1]
 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 2)) As [M2]
 , Reverse(ParseName(Replace(Reverse(MachineName), ' ', '.'), 3)) As [M3]

  FROM  (Select MachineName from @Sample
  ) As [x] 

Kick-start your career in SQL with the Intellipaat's perfect SQL Course now!