SQL Splitting Data into multiple rows and columns?

Create a Function as below

CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(max), @delimiter varchar(100) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value nvarchar(max)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END





NOW u can use this function in ur store proc as follows


-- =============================================
-- Author: <Amit,,Amit>
-- Create date: <April 23,2011,,>
-- Description: <Select System Vales,,>
-- =============================================
CREATE PROCEDURE [dbo].[SP_UpdateEmployee]
-- Add the parameters for the stored procedure here

@strpersonphonepar text = Null,

AS
BEGIN
declare @items table
(
position int ,
value varchar(max)
)
declare @itemvalues table
(
position int ,
value varchar(max)
)
declare @EmployeeIdpar varchar(max)
declare @PersonIdpar varchar(max)


declare @IsActivepar varchar(max)
declare @CreatedBypar varchar(max)
declare @PersonEMail varchar(max)
declare @PhoneTypeIdpar varchar(max)
declare @PersonPhoneNumberpar varchar(max)
declare @itemvalue varchar(max)



----------INSERT PERSON PHONE DETAILS STARTS
insert into @items
select position,value from fn_split( @strpersonphonepar,'=')
while((select count(*) from @items)>0)
begin
set @itemvalue = (select top 1 value from @items)
insert into @itemvalues
select position,value from fn_split(@itemvalue,',')

set @PhoneTypeIdpar = (select value from @itemvalues where position = 1)
set @PersonPhoneNumberpar =(select value from @itemvalues where position = 2)
set @IsActivepar =(select value from @itemvalues where position = 3)
set @CreatedBypar =(select value from @itemvalues where position = 4)
set @IDCheckNullpar =(select value from @itemvalues where position = 5)
IF (@IDCheckNullpar IS NULL) OR (@IDCheckNullpar = 'NULL')
BEGIN
INSERT INTO PersonPhone(PersonId,PhoneTypeId, PersonPhoneNumber,
IsActive,CreatedBy,CreatedDate)
VALUES(@PersonId, @PhoneTypeIdpar, @PersonPhoneNumberpar,
@IsActivepar, @CreatedBypar, GETDATE())
END

ELSE

BEGIN

UPDATE PersonPhone SET PhoneTypeId = @PhoneTypeIdpar, PersonPhoneNumber = @PersonPhoneNumberpar,
IsActive = @IsActivepar, LastModifiedBy = @CreatedBypar,LastModifiedDate = GETDATE()

WHERE PersonPhoneId = @IDCheckNullpar

END

delete from @Itemvalues
delete from @items where value = @itemvalue
end

----------INSERT PERSON PHONE DETAILS ENDS

END