Sql Doubt

rajanandal

Hi everyone, my table is 6219 HBO 02 0 6220 HBO1 0201 6219 6221 HEAO 0202 6219 6222 HTS 0203 6219 6231 MEAO 0403 6228 6232 MTS 0404 6228 6234 Midde 05 0 6235 Overi 06 0 6228 MBO 04 0 6236 WO 10 0 6237 Post 07 0 6239 Basi NULL NULL 6448 Athe 05001 6234 but i need like this, 6219 HBO 02 0 6220 HBO1 0201 6219 6221 HEAO 0202 6219 6222 HTS 0203 6219 6228 MBO 04 0 6231 MEAO 0403 6228 6232 MTS 0404 6228 6234 Midde 05 0 6448 Athe 05001 6234 6235 Overi 06 0 6236 WO 10 0 6237 Post 07 0 6239 Basi NULL NULL

Nothing is Impossible. Keep always Smiling... :)

Lost User

And?

rajanandal

that is all child records should be displayed next to parent.

Nothing is Impossible. Keep always Smiling... :)

dxlee

If you tell us that the fourth field is the parent's ID (and the first field is its ID), then we will have an easier time trying to figure out what you want.

modified on Tuesday, December 15, 2009 10:57 AM

Niladri_Biswas

Try this. Inputs:

declare @t table(childid int,name varchar(20),code int,parentid int)
insert into @t values (6219,'HBO',02,0)
insert into @t values(6220,'HBO1', 0201,6219)
insert into @t values(6221,'HEAO',0202,6219)
insert into @t values(6222,'HTS',0203,6219)
insert into @t values(6231,'MEAO',0403,6228)
insert into @t values(6232,'MTS',0404,6228)
insert into @t values(6234,'Midde',05,0)
insert into @t values(6235,'Overi',06,0)
insert into @t values(6228,'MBO',04,0)
insert into @t values(6236,'WO',10,0)
insert into @t values(6237,'Post',07,0)
insert into @t values(6239,'Basi',NULL,NULL)
insert into @t values(6448,'Athe',05001,6234)
select * from @t

Query:

;with cte as
(
select
cast(t1.childid as varchar(1000)) [path]
, t1.childid
,t1.name
,t1.code
,t1.parentid
,0 AS [Level] from @t t1 where parentid = 0 or parentid is null

union all
select 
	cast(\[path\] + '/' + cast(t1.childid as varchar(1000)) as varchar(1000)) \[path\]
	,t1.childid
	,t1.name
	,t1.code
	,t1.parentid 
	,c.\[Level\]+1 AS \[Level\]from @t t1
join cte c
on c.childid = t1.parentid

)
select childid,name,code,parentid
from cte order by [path]

Output:

childid name code parentid
6219 HBO 2 0
6220 HBO1 201 6219
6221 HEAO 202 6219
6222 HTS 203 6219
6228 MBO 4 0
6231 MEAO 403 6228
6232 MTS 404 6228
6234 Midde 5 0
6448 Athe 5001 6234
6235 Overi 6 0
6236 WO 10 0
6237 Post 7 0
6239 Basi NULL NULL

:)

Niladri Biswas

rajanandal

Thanks for ur timely help...

Nothing is Impossible. Keep always Smiling... :)