write sql tree structure .. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

write sql tree structure ..

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2009-08-25 : 00:20:22

ID-----Level -------Name
1-------0 ---------- A
2-------1----------- A1
8-------0----------- B
5-------1------------A2
9-------8------------B1
11------1-----------A3
12------1-----------A4
10------8-----------B2

Now i want to have tree structure:
ID-----Level -------Name
1-------0 ---------- A
2-------1----------- A1
5-------1------------A2
11------1-----------A3
12------1-----------A4
8-------0----------- B
9-------8------------B1
10------8-----------B2

Any one help me write to sql statement ! Thanks you very much.

zimu312500
Starting Member

1 Post

Posted - 2009-08-25 : 01:25:25

SELECT * FROM TABLE ORDER BY Name

I think it is easy to everyone , you should try it yourself!O(n_n)O

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-25 : 01:29:59

use order by name

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-08-25 : 01:39:01

ORDER BY CASE WHEN LEVEL=0 THEN ID ELSE LEVEL END , LEVEL, ID

Rahul Shinde

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-25 : 01:52:20

see this shinde
it will not work in this case
declare @tab table( ID int,Level int,Name varchar(12))
insert into @tab select
1, 0, 'A' union all select
2, 1, 'A1' union all select
8, 0, 'B' union all select
5, 1, 'A2' union all select
9, 8, 'B1' union all select
11,1, 'A3' union all select
12,1, 'A4' union all select
10,1, 'B2'

select * from @tab ORDER BY CASE WHEN LEVEL=0 THEN ID ELSE LEVEL END , LEVEL, ID

pamyral_279
Posting Yak Master

143 Posts

Posted - 2009-08-25 : 03:45:44

Thanks bklr and ra.shinde very much.
"select * from @tab ORDER BY CASE WHEN LEVEL=0 THEN ID ELSE LEVEL END , LEVEL, ID" ==> That's good !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-25 : 03:49:37

You use the word "tree", so i assume you will have more than 2 levels ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources