September 4, 2012 at 9:48 am
I have the following table
CREATE TABLE #TEMP(SECTION VARCHAR(10),
GROUPNO VARCHAR(25),
SUFFIXNO VARCHAR(20))
INSERT INTO #TEMP
SELECT '123','25676','001'
UNION ALL
SELECT '167','25676','002'
UNION ALL
SELECT '189','78986','005 THROUGH 020'
UNION ALL
SELECT '778','68862','020'
UNION ALL
SELECT '1899','67782','010 THROUGH 030'
UNION ALL
SELECT '6782','78864','900'
UNION ALL
SELECT '1280','78886','015'
UNION ALL
SELECT '8906','78899','011 THROUGH 029'
UNION ALL
SELECT '0988','78821','129'
UNION ALL
SELECT '8900','89901','230 THROUGH 250'
results are like:
12325676001
16725676002
18978986005 THROUGH 020
77868862020
189967782010 THROUGH 030
678278864900
128078886015
890678899011 THROUGH 029
098878821129
890089901230 THROUGH 250
But i need to get those suffixes which say 'through'. for example in section 189 i should get all suffixes from 005 to 020 like 005,006,007,.......019,020 maintaing other columns same.
Can anyone help me in this?
Thanks in advance
Kumar
September 4, 2012 at 11:11 am
Nice job posting ddl and sample data. The description was a little confusing but after a few reads I think I understood it.
This can be accomplished using a combination of a splitter and a tally table.
;with cte as
(
select *
from #TEMP t
cross apply dbo.DelimitedSplit8K(SuffixNo, ' THROUGH ')
)
, cteAgg as
(
select Section, GroupNo, SuffixNo, Min(Item) as MinItem, Max(Item) as MaxItem
from cte
where ltrim(rtrim(Item)) <> 'THROUGH'
group by Section, GroupNo, SuffixNo
)
select *
from cteAgg
join tally on tally.N <= cteAgg.MaxItem
You can read about the tally table here. http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D
To read about the string splitter see the link in my signature about splitting strings.
You should read both of these articles very carefully and understand the concepts presented. It will be your phone ringing in the middle of the night when production goes haywire. You don't you response to be "well some guy on the internet told me to do it like this". 😛
_______________________________________________________________
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/
September 4, 2012 at 12:45 pm
Thanks a lot for your reply!
Getting error at "dbo.DelimitedSplit8K" and may i know what is Item in your query. I am new to these concepts.
Can you please eloborate...
Thanks!
Kumar
September 4, 2012 at 12:50 pm
Kumar SQL (9/4/2012)
Thanks a lot for your reply!Getting error at "dbo.DelimitedSplit8K" and may i know what is Item in your query. I am new to these concepts.
Can you please eloborate...
Thanks!
Kumar
No I can't elaborate. I suggested you read two articles that explains them. It is obvious you did not.
You can read about the tally table here. http://www.sqlservercentral.com/articles/T-SQL/62867/
To read about the string splitter see the link in my signature about splitting strings.
You should read both of these articles very carefully and understand the concepts presented. It will be your phone ringing in the middle of the night when production goes haywire. You don't you response to be "well some guy on the internet told me to do it like this". [Tongue]
_______________________________________________________________
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/
September 4, 2012 at 1:38 pm
I did however find a bug in my code. You should change the final query to be this.
select *
from cteAgg
join tally on tally.N >= cteAgg.MinItem and tally.N <= cteAgg.MaxItem
I forgot to include the >= check in the first post.
_______________________________________________________________
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/
September 4, 2012 at 7:49 pm
You can also dispense with the string split and do it with just a tally table like this:
;WITH MyTemp AS (
SELECT SECTION, GROUPNO, SUFFIXNO, [First], [Last]
FROM #TEMP
CROSS APPLY (SELECT REVERSE(SUFFIXNO)) a(RSUFFIXNO)
CROSS APPLY (
SELECT [First]=CAST(SUBSTRING(SUFFIXNO, 1, CHARINDEX(' ', SUFFIXNO)) AS INT)
,[Last]=CAST(REVERSE(SUBSTRING(RSUFFIXNO, 1, CHARINDEX(' ', RSUFFIXNO))) AS INT)) b
),
Tally (n) AS (
SELECT TOP (
SELECT MAX([Last] - [First] + 1)
FROM MyTemp)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM sys.all_columns
)
SELECT SECTION, GROUPNO, SUFFIXNO
,NEWSUFFIXNO=CASE WHEN CHARINDEX('THROUGH', SUFFIXNO) <> 0
THEN RIGHT('000' + CAST(n + [First] AS VARCHAR), 3)
ELSE SUFFIXNO END
FROM MyTemp
CROSS APPLY (SELECT n FROM Tally WHERE n BETWEEN 0 AND [Last]-[First]) a
ORDER BY SECTION, GROUPNO, n + [First]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply