How to retrieve data
-
Hi,
I have a table named 't3' as below:
id text parentid
0rootNULL
1module0
2bxz1
3modxyz0
4submod1
5xyz3
from this I want to fetch the data like as below:-
id text parentid
0 Root NULL
1 Module 0
2 bxv 1
4 submod 1
3 modxyz 0
5 xyz 3
Pls tell how i can fetch the data as above from my table t3.
-
su_kumar11 (11/5/2008)
Hi,I have a table named 't3' as below:
id text parentid
0rootNULL
1module0
2bxz1
3modxyz0
4submod1
5xyz3
from this I want to fetch the data like as below:-
id text parentid
0 Root NULL
1 Module 0
2 bxv 1
4 submod 1
3 modxyz 0
5 xyz 3
Pls tell how i can fetch the data as above from my table t3.
what is the sort sequence here??
kshitij kumar
kshitij@krayknot.com
www.krayknot.com -
with CTE(id , parentid ,[text], fullpath)as (
select id , parentid ,[text], cast([text] as varchar(max))
from t3
where parentid is null
union all
select p.id , p.parentid ,p.[text], c.fullpath+'/'+cast(p.[text] as varchar(max))
from CTE c
inner join t3 p on p.parentid=c.id)
select id ,[text], parentid
from CTE
order by fullpath
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
It's a hierarchical sequence and I believe Mark has solved it using a classic recursive CTE.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply