sql - Column Combination -

i know there sql this:

sample table:

id   firstname   lastname 1    jones       smith 2    paul        tabunjong 3    john        parks 

sql result:

id   name 1    jones smith 2    paul tabunjong 3    john parks 

now, possible have reverse of it? this:

sample table:

id   name 1    jones smith 2    paul tabunjong 3    john parks 

sql result:

id   firstname   lastname 1    jones       smith 2    paul        tabunjong 3    john        parks 

another one: possible have this:

sample table:

id   corporatenames 1    jones smith; anna tomson; tonny parker 2    paul tabunjong; poncho pilato 3    john parks; berto taborjakol 

sql result:

id   firstname   lastname 1    jones       smith 1    anna        tomson  1    tonny       parker 2    paul        tabunjong 2    poncho      pilato 3    john        parks 3    berto       taborjakol 

yes, can write split function , use parse data. below sample split function using substring() function:

create function dbo.split (     @rowdata nvarchar(2000),     @spliton nvarchar(5) )   returns @rtnvalue table  (     id int identity(1,1),     data nvarchar(100) )    begin      declare @cnt int     set @cnt = 1      while (charindex(@spliton,@rowdata)>0)     begin         insert @rtnvalue (data)         select              data = ltrim(rtrim(substring(@rowdata,1,charindex(@spliton,@rowdata)-1)))          set @rowdata = substring(@rowdata,charindex(@spliton,@rowdata)+1,len(@rowdata))         set @cnt = @cnt + 1     end      insert @rtnvalue (data)     select data = ltrim(rtrim(@rowdata))      return end