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.
| Author |
Topic |
|
nbalraj
Starting Member
21 Posts |
 Posted - 2011-03-18 : 17:41:05
|
| Hello,I have this table with the below record setPrimaryID ParentID PrimaryName PrimaryAmount1 0 One 1002 1 Two 2003 0 Three 3004 1 Four 4005 3 Five 500I want the result asPrimaryID ParentID PrimaryName PrimaryAmount1 0 One 1002 1 Two 2004 1 Four 4003 0 Three 3005 3 Five 500can anyone help me with some directions. as you can see first record should be the primaryid record followed with the records that has that primary id as parentid.thanks, |
|
|
pduffin
Yak Posting Veteran
68 Posts |
Posted - 2011-03-18 : 20:09:40
|
| create table #tmppmd (pid int,parid int)insert into #tmppmd values (1,0 )insert into #tmppmd values (2,1 )insert into #tmppmd values (3,0 )insert into #tmppmd values (4,1 )insert into #tmppmd values (5,3 )select *from ( select pmd1.pid as id,pmd1.* from #tmppmd pmd1 where parid = 0 union all select pmd2.parid as id,pmd2.* from #tmppmd pmd1 join #tmppmd pmd2 on pmd2.parid = pmd1.pid ) aorder by a.id,a.pid |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-03-18 : 23:53:23
|
will you have more than 2 level ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
nbalraj
Starting Member
21 Posts |
Posted - 2011-03-20 : 01:02:33
|
| Nope, there will be only 1 level |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-20 : 08:07:17
|
If there will be only two levels (which would mean that the first level should always be zero to avoid accidental level growth), you could sort like this: case when parentId = 0 then cast(id as varchar(32)) else cast (parentId as varchar(32)) + right('00000'+cast(id as varchar(32)),5) end, idBut, this is not the most ideal if you want to have any hope of extensibility. If you do, I would think in terms of a recursive query to figure out the ordering. Not clear in my mind how to do it, but I am sure many others here can do it with half their brain tied behind their back. |
|
|
|
nbalraj
Starting Member
21 Posts |
Posted - 2011-03-22 : 12:46:57
|
| pduffin's idea helped me. Thanks for all your inputs. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
grouping by parent ID