January 8, 2014 at 1:02 pm
Hi all,
I have a table with below values.
create table #temp (fileno int, actfile int)
insert into #temp values (10,0)
insert into #temp values (20,30)
insert into #temp values (30,40)
insert into #temp values (40,50)
insert into #temp values (60,90)
insert into #temp values (70,100)
insert into #temp values (80,40)
insert into #temp values (90,60)
insert into #temp values (100,20)
insert into #temp values (110,0)
If i give input as 20, i need to get result as mentioned below.
FileNoActFile
2030
3040
4050
10020
8040
70100
Result explaination: If i give 20, i need to get all values corresponding to 20 from fileno and actfile columns.
Again, for that 20, i will take values for 30 from both the columns. This will follow till the last value existence.
If we give 40 as input, i should get below output.
FileNoActFile
4050
3040
8040
2030
10020
70100
Kindly help me here to get the output. Please let me know if you need anything else.
Regards,
Karthik.
SQL Developer.
January 8, 2014 at 2:43 pm
You are basically describing a classic recursive cte situation but you have a twist in that the recursion can go both directions.
Here is the basic cte.
create table #temp (fileno int, actfile int)
insert into #temp values (10,0)
insert into #temp values (20,30)
insert into #temp values (30,40)
insert into #temp values (40,50)
insert into #temp values (60,90)
insert into #temp values (70,100)
insert into #temp values (80,40)
insert into #temp values (90,60)
insert into #temp values (100,20)
insert into #temp values (110,0)
declare @SearchVal int = 20;
with cte as
(
select fileno, actfile
from #temp
where fileno = @SearchVal
union all
select t1.fileno, t1.actfile
from #temp t1
join cte on t1.fileno = cte.actfile
)
select * from cte
drop table #temp
Now we need to able to do the recursion in reverse as well so something like this should work.
create table #temp (fileno int, actfile int)
insert into #temp values (10,0)
insert into #temp values (20,30)
insert into #temp values (30,40)
insert into #temp values (40,50)
insert into #temp values (60,90)
insert into #temp values (70,100)
insert into #temp values (80,40)
insert into #temp values (90,60)
insert into #temp values (100,20)
insert into #temp values (110,0)
declare @SearchVal int = 20;
with cte as
(
select fileno, actfile from #temp where fileno = @SearchVal
union all
select t1.fileno, t1.actfile
from #temp t1
join cte on t1.fileno = cte.actfile
)
, cte2 as
(
select fileno, actfile from #temp where actfile = @SearchVal
union all
select t1.fileno, t1.actfile
from #temp t1
join cte2 on cte2.fileno = t1.actfile
)
select * from cte
union
select * from cte2
drop table #temp
This gets almost what you state you are looking for. It does not return 80, 40 but I can't understand from your data why you think that one should be returned.
_______________________________________________________________
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 5:31 pm
Sean Lange (1/8/2014)
...This gets almost what you state you are looking for. It does not return 80, 40 but I can't understand from your data why you think that one should be returned.
This way does, using 2 recursive legs in the rCTE.
WITH rCTE AS
(
SELECT n=1, FileNo, ActFile, a=NULL, b=NULL
FROM #temp
WHERE FileNo = 20
UNION ALL
SELECT n+1, b.FileNo, b.ActFile, a.FileNo, a.ActFile
FROM rCTE a
JOIN #temp b ON a.ActFile = b.FileNo
UNION ALL
SELECT n+1, b.FileNo, b.ActFile, a.FileNo, a.ActFile
FROM rCTE a
JOIN #temp b ON b.ActFile = a.FileNo
WHERE n < 4
)
SELECT DISTINCT FileNo, ActFile
FROM rCTE
ORDER BY FileNo;
Note that you must set your limiter (n < 4) to the number of levels deep you want to check for. The DISTINCT is needed because the reverse traversal generates a lot of duplicate entries the deeper you search.
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
January 9, 2014 at 2:53 am
Hi, Thanks for the query. I will use this and let you know if i have any doubts.
Regards,
Karthik.
SQL Developer.
January 9, 2014 at 7:33 am
dwain.c (1/8/2014)
Sean Lange (1/8/2014)
...This gets almost what you state you are looking for. It does not return 80, 40 but I can't understand from your data why you think that one should be returned.
This way does, using 2 recursive legs in the rCTE.
WITH rCTE AS
(
SELECT n=1, FileNo, ActFile, a=NULL, b=NULL
FROM #temp
WHERE FileNo = 20
UNION ALL
SELECT n+1, b.FileNo, b.ActFile, a.FileNo, a.ActFile
FROM rCTE a
JOIN #temp b ON a.ActFile = b.FileNo
UNION ALL
SELECT n+1, b.FileNo, b.ActFile, a.FileNo, a.ActFile
FROM rCTE a
JOIN #temp b ON b.ActFile = a.FileNo
WHERE n < 4
)
SELECT DISTINCT FileNo, ActFile
FROM rCTE
ORDER BY FileNo;
Note that you must set your limiter (n < 4) to the number of levels deep you want to check for. The DISTINCT is needed because the reverse traversal generates a lot of duplicate entries the deeper you search.
Nice Dwain. I hope I never have to do something so completely convoluted in the real world. This to me just screams of improper normalization.
_______________________________________________________________
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/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply