January 7, 2014 at 2:57 pm
Have the following data in the table.
Declare @tblRawData as table
(
WorkItemId int,
WorkItemDescription varchar(500)
)
insert into @tblRawData select 10110, 'Parallel Work Items: 10111, 10112; Related Work Items: 10113, 10114;'
insert into @tblRawData select 10111, 'Parallel Work Items: 10110, 10112; Related Work Items: 10115;'
insert into @tblRawData select 10112, 'Parallel Work Items: 10110, 10111; Related Work Items: 10116, 10117,10118;'
The expected result is as below, if any single workitemID is specified..eg: If WorkItem 10110 is passed, it shld return the below..
WorkItemID ------ RelatedWorkItem
10110 ------ 10113,10114
10111 ------10115
10112 ------10116, 10117,10118
Attempted with the following code, but able to extract the first row combinations...
Declare @projectid int = 10110
Declare @Desc varchar(6000)
Declare @FinalProjectList as table
(
ProjectID int,
RelatedWorkItems varchar(500)
)
Declare @FirstStep varchar(4000)
Declare @ParallelWorkItemString varchar(4000)
Declare @RelatedWorkItemString varchar(4000)
DECLARE @C varchar(100)
select @Desc =ProjectDescription from @tblRawData where projectid = @projectid
select @FirstStep = substring(@desc, charindex('Parallel', @desc), len(@desc))
--select @FirstStep
select @ParallelWorkItemString = substring(@FirstStep, 1,charindex(';', @FirstStep))
--select @FirstStepA
select @RelatedWorkItemString = substring(@FirstStep, charindex('Related', @FirstStep), len(@FirstStep))
SET @C = @ParallelWorkItemString
SET @C = Replace(Replace(@c,':','_'),';','_');
SELECT @ParallelWorkItemString = SUBSTRING(
@C,
CHARINDEX('_', @C) + 1,
LEN(@c) - CHARINDEX('_', @C) - CHARINDEX('_', REVERSE(@c))
)
SET @C = @RelatedWorkItemString
SET @C = Replace(Replace(@c,':','_'),';','_');
SELECT @RelatedWorkItemString = SUBSTRING(
@C,
CHARINDEX('_', @C) + 1,
LEN(@c) - CHARINDEX('_', @C) - CHARINDEX('_', REVERSE(@c))
)
insert into @FinalProjectList(ProjectID,RelatedWorkItems)
select @projectID,ltrim(rtrim(@RelatedWorkItemString))
insert into @FinalProjectList(ProjectID)
select ltrim(rtrim(val)) from dbo.Split(',',ltrim(rtrim(@ParallelWorkItemString))) where val not in (Select projectid from @FinalProjectList)
select * from @FinalProjectList
Getting the result as below. The split function used in the above code is comma split function.
WorkItemID ------------- RelatedWorkItem
10110 10113,10114
10111 null
10112 null
Any help is appreciated.
January 7, 2014 at 3:03 pm
satishchandra (1/7/2014)
Have the following data in the table.
Declare @tblRawData as table
(
WorkItemId int,
WorkItemDescription varchar(500)
)
insert into @tblRawData select 10110, 'Parallel Work Items: 10111, 10112; Related Work Items: 10113, 10114;'
insert into @tblRawData select 10111, 'Parallel Work Items: 10110, 10112; Related Work Items: 10115;'
insert into @tblRawData select 10112, 'Parallel Work Items: 10110, 10111; Related Work Items: 10116, 10117,10118;'
The expected result is as below, if any single workitemID is specified..eg: If WorkItem 10110 is passed, it shld return the below..
WorkItemID ------ RelatedWorkItem
10110 ------ 10113,10114
10111 ------10115
10112 ------10116, 10117,10118
It looks like maybe you switch 10111 and 10112? Was this intentional or an accident when posting?
Attempted with the following code, but able to extract the first row combinations...
Declare @projectid int = 10110
Declare @Desc varchar(6000)
Declare @FinalProjectList as table
(
ProjectID int,
RelatedWorkItems varchar(500)
)
Declare @FirstStep varchar(4000)
Declare @ParallelWorkItemString varchar(4000)
Declare @RelatedWorkItemString varchar(4000)
DECLARE @C varchar(100)
select @Desc =ProjectDescription from @tblRawData where projectid = @projectid
select @FirstStep = substring(@desc, charindex('Parallel', @desc), len(@desc))
--select @FirstStep
select @ParallelWorkItemString = substring(@FirstStep, 1,charindex(';', @FirstStep))
--select @FirstStepA
select @RelatedWorkItemString = substring(@FirstStep, charindex('Related', @FirstStep), len(@FirstStep))
SET @C = @ParallelWorkItemString
SET @C = Replace(Replace(@c,':','_'),';','_');
SELECT @ParallelWorkItemString = SUBSTRING(
@C,
CHARINDEX('_', @C) + 1,
LEN(@c) - CHARINDEX('_', @C) - CHARINDEX('_', REVERSE(@c))
)
SET @C = @RelatedWorkItemString
SET @C = Replace(Replace(@c,':','_'),';','_');
SELECT @RelatedWorkItemString = SUBSTRING(
@C,
CHARINDEX('_', @C) + 1,
LEN(@c) - CHARINDEX('_', @C) - CHARINDEX('_', REVERSE(@c))
)
insert into @FinalProjectList(ProjectID,RelatedWorkItems)
select @projectID,ltrim(rtrim(@RelatedWorkItemString))
insert into @FinalProjectList(ProjectID)
select ltrim(rtrim(val)) from dbo.Split(',',ltrim(rtrim(@ParallelWorkItemString))) where val not in (Select projectid from @FinalProjectList)
select * from @FinalProjectList
Getting the result as below. The split function used in the above code is comma split function.
WorkItemID ------------- RelatedWorkItem
10110 10113,10114
10111 null
10112 null
Any help is appreciated.
What does your split function look like? There are lots of them out there. If it contains a cursor, a while loop or xml I would urge you to look at the article in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 7, 2014 at 3:13 pm
Now I see what you are trying to do. Does something like this work?
select WorkItemId
, replace(SUBSTRING(WorkItemDescription, CHARINDEX('Related Work Items', WorkItemDescription) + 20, LEN(WorkItemDescription)), ';', '')
from @tblRawData
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 7, 2014 at 3:21 pm
Is there any chance you can normalize this data instead of fighting what looks like a text file all the time?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 7, 2014 at 6:16 pm
Hi
I think what you are after is related work items if the workitemid you are specifying is listed as a parallelwork item as well as the original work item.
Hope the following helps.
Declare @WorkItemId int = 10110
;WITH CTEFindParallel AS
(SELECTWorkItemId
FROM @tblRawData
WHERE CHARINDEX(CAST(@WorkItemId AS VARCHAR(5)),SUBSTRING (WorkItemDescription,1,charindex(';',WorkItemDescription,1)),1) >0)
SELECTRD.WorkItemId,
SUBSTRING(RD.WorkItemDescription,CHARINDEX('Related Work Items:',RD.WorkItemDescription,1)+20,(LEN(RD.WorkItemDescription)-(CHARINDEX('Related Work Items:',RD.WorkItemDescription,1)+20))) 'RelatedWorkItem'
FROM @tblRawData RD
INNER JOIN CTEFindParallel FP
ON RD.WorkItemId = FP.WorkItemId
UNION
SELECTRD1.WorkItemId,
SUBSTRING(RD1.WorkItemDescription,CHARINDEX('Related Work Items:',RD1.WorkItemDescription,1)+20,(LEN(RD1.WorkItemDescription)-(CHARINDEX('Related Work Items:',RD1.WorkItemDescription,1)+20))) 'RelatedWorkItem'
FROM @tblRawData RD1
WHERE RD1.WorkItemId = @WorkItemId
January 8, 2014 at 9:48 am
The solution from MelroyV, worked for me. Thanks a lot, melroy.
Thank you Sean for your inputs/suggestions.
Your suggested solution was close too, but I was looking for traversed results. Apologies, if my post did not provide complete details.
January 8, 2014 at 11:30 am
satishchandra (1/8/2014)
The solution from MelroyV, worked for me. Thanks a lot, melroy.Thank you Sean for your inputs/suggestions.
Your suggested solution was close too, but I was looking for traversed results. Apologies, if my post did not provide complete details.
Glad you've got a solution but back to the other problem. Please post your "Split" function. Dollars-to-donuts says that it will be a performance problem and we know how to fix it.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2014 at 1:06 pm
Here's a solution with only one table scan.
Declare @WorkItemId int = 10110
SELECT
WorkItemId,
RelatedWorkItem = LEFT(z.RelatedWorkItem,LEN(z.RelatedWorkItem)-1)
FROM @tblRawData
CROSS APPLY (SELECT RWIStart = CHARINDEX('; Related Work',WorkItemDescription)) x
CROSS APPLY (SELECT PWIlist = REPLACE(LEFT(WorkItemDescription,RWIStart-1),'Parallel Work Items: ','')) y
CROSS APPLY (SELECT RelatedWorkItem = SUBSTRING(WorkItemDescription,RWIStart+22,8000)) z
WHERE WorkItemId = @WorkItemId
OR y.PWIlist LIKE '%'+CAST(@WorkItemId AS varCHAR(5))+'%'
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 8, 2014 at 1:40 pm
Jeff, Here is the code for split function..
CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s-2 VARCHAR(MAX))
RETURNS @t TABLE
(
val VARCHAR(MAX)
)
AS
BEGIN
DECLARE @xml XML
SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'
INSERT INTO @t(val)
SELECT DISTINCT r.value('.','VARCHAR(MAX)') as Item
FROM @xml.nodes('//root/r') AS RECORDS(r)
RETURN
END
January 8, 2014 at 2:14 pm
satishchandra (1/8/2014)
Jeff, Here is the code for split function..
Take a look at the link provided by Jeff above or the same link in my signature about splitting strings. In there you will find a new function DelimitedSplit8K that will blow the doors of that xml splitter for performance. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 8, 2014 at 2:24 pm
Sean Lange (1/8/2014)
satishchandra (1/8/2014)
Jeff, Here is the code for split function..Take a look at the link provided by Jeff above or the same link in my signature about splitting strings. In there you will find a new function DelimitedSplit8K that will blow the doors of that xml splitter for performance. 😀
- with the evidence in a handy stare & compare graph, to boot 😉
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply