Click here to Skip to main content
15,562,816 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
In a SQL report I have built, I have data in one cell of a column whihc I would like to split into multiple columns and rows.

The data is in the "State" column in the below table

Agent ID Date Schedule Item Type
106 08/03/2011 Break: 1st 15 Start 09:30 End 09:45, Exception: Daily Meeting Start 10:30 End 10:45, Break: Lunch Start 12:00 End 13:00, Break: 2nd 15 Start 14:00 End 14:15, Exception: Daily Meeting Start 14:30 End 14:45



I need it to be presented in the following format



Agent ID


Date


Schedule Item Type


Item Name


Start


End

106


08/03/2011


Break


1st 15


09:30


09:45

106


08/03/2011


Exception


Daily Meeting


10:30


10:45

106


08/03/2011


Break


Lunch


12:00


13:00

106


08/03/2011


Break


2nd 15


14:00


14:15

106


08/03/2011


Exception


Daily Meeting


14:30


14:45


Any thoughts on how I can achieve this?
Posted

1 solution

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
 
Share this answer
 
Comments
amit28august 16-Jun-11 8:02am    
This Function will return row by row value......... in the @item table created above as a temp table on the basis of the split character u pass

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900