query required
-
------ create table
create table test222(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
insert into test222 values (1, '2323', 0, 'iam a boy')
insert into test222 values (2, '23231000', 1, 'boy')
insert into test222 values (3, '23232', 1, 'boo')
insert into test222 values (4, '232321', 3, 'bo')
insert into test222 values (5, '23232110', 4, 'boyy')
insert into test222 values (6, '23232190', 4, 'gril')
insert into test222 values (7, '232329', 3, 'body')
insert into test222 values (8, '23232910', 7, 'girll')
insert into test222 values (9, '23232990', 7, 'boy')
insert into test222 values (10, '23233000', 1, 'bo')
insert into test222 values (11, '232390', 1, 'nh')
insert into test222 values (12, '23239010', 10, 'ui')
insert into test222 values (13, '23239020', 10, 'dert')
insert into test222 values (14, '23239030', 10, 'hyui')
insert into test222 values (15, '23239040', 10, 'nji')
insert into test222 values (16, '23239090', 10, 'vfr')
iam searching for 2323...
the exepected op is as follows
1 (1 should have childs like 2,3,10,11 as its child))
2 ( should have nothing) 3(should have 4,7 as its child) 10(should have 12,13,14,15,16 as its child) 11
4(should have 5,6 as its child) 7(should have 8,9 as its child) 12
5 , 6 8 9 13
14
15
16
-
peterausger (4/27/2015)
------ create tablecreate table test222(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
insert into test222 values (1, '2323', 0, 'iam a boy')
insert into test222 values (2, '23231000', 1, 'boy')
insert into test222 values (3, '23232', 1, 'boo')
insert into test222 values (4, '232321', 3, 'bo')
insert into test222 values (5, '23232110', 4, 'boyy')
insert into test222 values (6, '23232190', 4, 'gril')
insert into test222 values (7, '232329', 3, 'body')
insert into test222 values (8, '23232910', 7, 'girll')
insert into test222 values (9, '23232990', 7, 'boy')
insert into test222 values (10, '23233000', 1, 'bo')
insert into test222 values (11, '232390', 1, 'nh')
insert into test222 values (12, '23239010', 10, 'ui')
insert into test222 values (13, '23239020', 10, 'dert')
insert into test222 values (14, '23239030', 10, 'hyui')
insert into test222 values (15, '23239040', 10, 'nji')
insert into test222 values (16, '23239090', 10, 'vfr')
iam searching for 2323...
the exepected op is as follows
1 (1 should have childs like 2,3,10,11 as its child))
2 ( should have nothing) 3(should have 4,7 as its child) 10(should have 12,13,14,15,16 as its child) 11
4(should have 5,6 as its child) 7(should have 8,9 as its child) 12
5 , 6 8 9 13
14
15
16
You have a combination of two things here. First you will need a recursive cte to get your children. Then you need to generate a comma separated list. There are thousands and thousands of examples of a recursive cte all over the place. For an excellent explanation of generating a comma delimited list take a look at this article. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
_______________________________________________________________
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/ -
peterausger (4/27/2015)
------ create tablecreate table test222(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
insert into test222 values (1, '2323', 0, 'iam a boy')
insert into test222 values (2, '23231000', 1, 'boy')
insert into test222 values (3, '23232', 1, 'boo')
insert into test222 values (4, '232321', 3, 'bo')
insert into test222 values (5, '23232110', 4, 'boyy')
insert into test222 values (6, '23232190', 4, 'gril')
insert into test222 values (7, '232329', 3, 'body')
insert into test222 values (8, '23232910', 7, 'girll')
insert into test222 values (9, '23232990', 7, 'boy')
insert into test222 values (10, '23233000', 1, 'bo')
insert into test222 values (11, '232390', 1, 'nh')
insert into test222 values (12, '23239010', 10, 'ui')
insert into test222 values (13, '23239020', 10, 'dert')
insert into test222 values (14, '23239030', 10, 'hyui')
insert into test222 values (15, '23239040', 10, 'nji')
insert into test222 values (16, '23239090', 10, 'vfr')
iam searching for 2323...
the exepected op is as follows
1 (1 should have childs like 2,3,10,11 as its child))
2 ( should have nothing) 3(should have 4,7 as its child) 10(should have 12,13,14,15,16 as its child) 11
4(should have 5,6 as its child) 7(should have 8,9 as its child) 12
5 , 6 8 9 13
14
15
16
Not really seeing what you are trying accomplish or what your result set should look like.
How do you link the parent/child records?
-
I think I see it now:
create table test222(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50));
insert into test222 values (1, '2323', 0, 'iam a boy');
insert into test222 values (2, '23231000', 1, 'boy');
insert into test222 values (3, '23232', 1, 'boo');
insert into test222 values (4, '232321', 3, 'bo');
insert into test222 values (5, '23232110', 4, 'boyy');
insert into test222 values (6, '23232190', 4, 'gril');
insert into test222 values (7, '232329', 3, 'body');
insert into test222 values (8, '23232910', 7, 'girll');
insert into test222 values (9, '23232990', 7, 'boy');
insert into test222 values (10, '23233000', 1, 'bo');
insert into test222 values (11, '232390', 1, 'nh');
insert into test222 values (12, '23239010', 10, 'ui');
insert into test222 values (13, '23239020', 10, 'dert');
insert into test222 values (14, '23239030', 10, 'hyui');
insert into test222 values (15, '23239040', 10, 'nji');
insert into test222 values (16, '23239090', 10, 'vfr');
select * from dbo.test222;
with basedata as (
select
[sid],
scode,
parentid,
sname,
sortkey = cast([sid] as varbinary(max))
from
dbo.test222
where
parentid = 0
union all
select
t2.[sid],
t2.scode,
t2.parentid,
t2.sname,
sortkey = bd.sortkey + cast(t2.[sid] as varbinary(max))
from
basedata bd
inner join dbo.test222 t2
on (bd.sid = t2.parentid)
)
select * from basedata order by sortkey;
-- d r o p table dbo.test222; -- remove the -- and the spaces in the word drop. Can't post the actual statement for some reason.
-
thanks lynn,
following is what i want..
2323 (2323 should have childs like 23231000,23232,23233000,232390 as its child))
23231000 ( should have nothing)
23232 (should have 232321,232329 as its child)
23233000(should have 23239010,23239020,23239030,23239040,23239090 as its child)
232390 ( should have nothing)
232321 (should have 23232110,23232190 as its child)
232329 (should have 23232910,23232990 as its child)
so the expected op is
2323,23231000,23232(232321(23232110,23232190),232329(23232910,23232990)),23233000(23239010,23239020,23239030,23239040,23239090),232390
-
peterausger (4/28/2015)
thanks lynn,following is what i want..
2323 (2323 should have childs like 23231000,23232,23233000,232390 as its child))
23231000 ( should have nothing)
23232 (should have 232321,232329 as its child)
23233000(should have 23239010,23239020,23239030,23239040,23239090 as its child)
232390 ( should have nothing)
232321 (should have 23232110,23232190 as its child)
232329 (should have 23232910,23232990 as its child)
so the expected op is
2323,23231000,23232(232321(23232110,23232190),232329(23232910,23232990)),23233000(23239010,23239020,23239030,23239040,23239090),232390
Still not sure what it is you want as output as it seems to change. Modified my code a bit and used the parented column provided. Not sure if this will work on your production data as You have only on parent/child chain in your sample data.
create table test222(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50));
insert into test222 values (1, '2323', 0, 'iam a boy');
insert into test222 values (2, '23231000', 1, 'boy');
insert into test222 values (3, '23232', 1, 'boo');
insert into test222 values (4, '232321', 3, 'bo');
insert into test222 values (5, '23232110', 4, 'boyy');
insert into test222 values (6, '23232190', 4, 'gril');
insert into test222 values (7, '232329', 3, 'body');
insert into test222 values (8, '23232910', 7, 'girll');
insert into test222 values (9, '23232990', 7, 'boy');
insert into test222 values (10, '23233000', 1, 'bo');
insert into test222 values (11, '232390', 1, 'nh');
insert into test222 values (12, '23239010', 10, 'ui');
insert into test222 values (13, '23239020', 10, 'dert');
insert into test222 values (14, '23239030', 10, 'hyui');
insert into test222 values (15, '23239040', 10, 'nji');
insert into test222 values (16, '23239090', 10, 'vfr');
--select * from dbo.test222;
with basedata as (
select
[sid],
scode,
cast(stuff(scode,1,0,replicate(N' ',0 * 4)) as nvarchar(4000)) scodeIndent,
parentid,
sname,
ReportLevel = 0,
sortkey = cast([sid] as varbinary(max))
from
dbo.test222
where
parentid = 0
union all
select
t2.[sid],
t2.scode,
cast(stuff(t2.scode,1,0,replicate(N' ',(bd.ReportLevel + 1) * 4)) as nvarchar(4000)) scodeIndent,
t2.parentid,
t2.sname,
ReportLevel = bd.ReportLevel + 1,
sortkey = bd.sortkey + cast(t2.[sid] as varbinary(max))
from
basedata bd
inner join dbo.test222 t2
on (bd.sid = t2.parentid)
)
select * from basedata order by sortkey;
-- you will need to add the d r o p of the table in a test environment. Can't post it from work.
-
A change to the end of Lynns query gives you the concatenated results you're after
with basedata as (
select
[sid],
scode,
cast(stuff(scode,1,0,replicate(N' ',0 * 4)) as nvarchar(4000)) scodeIndent,
parentid,
sname,
ReportLevel = 0,
sortkey = cast([sid] as varbinary(max))
from
dbo.test222
where
parentid = 0
union all
select
t2.[sid],
t2.scode,
cast(stuff(t2.scode,1,0,replicate(N' ',(bd.ReportLevel + 1) * 4)) as nvarchar(4000)) scodeIndent,
t2.parentid,
t2.sname,
ReportLevel = bd.ReportLevel + 1,
sortkey = bd.sortkey + cast(t2.[sid] as varbinary(max))
from
basedata bd
inner join dbo.test222 t2
on (bd.sid = t2.parentid)
),
results as (
select b1.scode,b1.ReportLevel,b1.sortkey,
b2.LeadReportLevel
from basedata b1
outer apply (select top 1 b3.ReportLevel from basedata b3 where b3.sortkey > b1.sortkey order by b3.sortkey) b2(LeadReportLevel)
)
select (select scode +
case when ReportLevel = 0 or ReportLevel = LeadReportLevel then ','
when ReportLevel > LeadReportLevel then replicate(')',ReportLevel - LeadReportLevel) + ','
when ReportLevel < LeadReportLevel then replicate('(',LeadReportLevel - ReportLevel)
else ''
end as "text()"
from results
order by sortkey
for xml path(''),type).value('./text()[1]','varchar(1000)') as result;
____________________________________________________
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply