April 16, 2015 at 5:48 am
dear all,
i have following query...
------ create table
create table test222(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
------ insert records
insert into test222 values (1, '11', 0, 'a')
insert into test222 values (2, '111', 1, 'ccc')
insert into test222 values (3, '1111', 2, 'c')
insert into test222 values (4, '11111', 3, 'd')
insert into test222 values (5, '11111', 4, 'c')
;WITH SInfo AS
(
SELECT sId
,scode
,ParentId
,sName
,CONVERT(nvarchar(800), scode) AS Hierarchy
FROM test222
WHERE ParentId = 0
UNION ALL
SELECT TH.sId
,TH.scode
,TH.ParentId
,TH.sName
,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))
FROM test222 TH
INNER JOIN SInfo ON SInfo.sId = TH.ParentId
)
Select sId,scode,ParentId,sName,Hierarchy
from SInfo
where sname like '%c'
op expected
2 111 1 ccc 11\111
5 111111 4 c 11\111\1111\11111\111111
thanks
peter
April 16, 2015 at 6:24 am
There are several ways to go about solving this. Here's one that's an ITVF that uses a recursive CTE to pull the children of a parent with the path. You can filter the output however you need to. The @intMaxLevels parameter is there as a safety.
if OBJECT_ID('dbo.SubordinatesWithPath', 'if') is not null drop function dbo.SubordinatesWithPath;
go
CREATE FUNCTION dbo.SubordinatesWithPath(@intRoot NVarchar(4),
@intMaxLevels Integer)
RETURNS TABLE
AS
RETURN (
WITH cteSubs AS (
SELECT sid, scode, ParentID, sname, 0 Level, CAST(N'/' + scode + N'/' AS NVarchar(MAX)) Path
FROM dbo.test222
WHERE parentid = @intRoot
UNION ALL
SELECT c.sid, c.scode, c.ParentID, c.sname, p.Level + 1, p.Path + c.scode + N'/'
FROM cteSubs p
INNER JOIN dbo.test222 c ON c.ParentID = p.sid
WHERE p.Level < @intMaxLevels
)
SELECT sid, scode, ParentID, sname, Level, Path
FROM cteSubs
);
go
You can then use it to query, starting with any parent.
SELECT sid, sCode, Level, ParentID, sname, Path
FROM dbo.SubordinatesWithPath(0, 100)
ORDER BY Level;
HTH
April 16, 2015 at 6:30 am
thanks, but can you please correct my query ??? i have created my query as a stored procedure so if you could help me to correct it , it will be of great help...
min and max value should come automatically dont want the user to feed in that...user will provide just c thats it...
April 16, 2015 at 6:38 am
That's no problem. Just take the query out of the function and place it in your stored procedure. Replace the @intRoot parameter with the hard-coded value 0 and either hard-code the value @intMaxLevels or simply remove it from the WHERE clause. You can apply your WHERE clause in the outer query and you should be all set.
April 16, 2015 at 6:48 am
thanks i did what you said,,, and following is what i am getting
21111ccc1/11/111/
311112c2/11/111/1111/
51111114c4/11/111/1111/11111/111111/
i just want ; my expected op is as follows..pls help me
21111ccc1/11/111/
51111114c4/11/111/1111/11111/111111/
April 16, 2015 at 6:52 am
Correct. The WHERE clause in the OP said you wanted the rows where sname ended with a C. If you look at the rows in your sample table, rows with sid 2, 3 and 5 all match that condition.
insert into test222 values (1, '11', 0, 'a')
insert into test222 values (2, '111', 1, 'ccc')
insert into test222 values (3, '1111', 2, 'c')
insert into test222 values (4, '11111', 3, 'd')
insert into test222 values (5, '11111', 4, 'c')
I did a double-take on it too, but I don't see how sid 3 would be removed from the result set because it does indeed end with a C. I figured it must have been an oversight or I was just missing it. You can put whatever condition on the outer WHERE clause to eliminate the rows you don't want.
April 16, 2015 at 6:59 am
WITH cteSubs AS (
SELECT sid, scode, ParentID, sname, 0 Level, CAST(N'/' + scode + N'/' AS NVarchar(MAX)) Path
FROM dbo.test222
WHERE parentid = 0
UNION ALL
SELECT c.sid, c.scode, c.ParentID, c.sname, p.Level + 1, p.Path + c.scode + N'/'
FROM cteSubs p
INNER JOIN dbo.test222 c ON c.ParentID = p.sid
-- WHERE p.Level < @intMaxLevels
)
SELECT sid, scode, ParentID, sname, Level, Path
FROM cteSubs where sname like '%c'
you are correct, but sid 5 has all the values of sid 3 so i dont want to display 3
its for a tree view.
in tree view
it will be
11_a
111_ccc
11_a
111_ccc
1111_c
11111_d
111111_c
so if i bring in 3 (see below) , un ncessarily there will be one more level, so i just want to eleminate that..
11_a
111_ccc
11_a
111_ccc
1111_c
11_a
111_ccc
1111_c
11111_d
111111_c
April 16, 2015 at 7:31 am
sorry for my mistake, let us consider this...
insert into test222 values (1, '11', 0, 'a')
insert into test222 values (2, '111', 1, 'ccc')
insert into test222 values (3, '1111', 2, 'c')
insert into test222 values (4, '11111', 0, 'd')
insert into test222 values (5, '111111', 4, 'c')
the expected op is
3 1111 2 c 11\111\1111
5 11111 4 c 11111\111111
parent id of 4 and 1 are 0s .. answer to your question is that....
April 16, 2015 at 11:50 pm
insert into test222 values (1, '11', 0, 'iam a boy')
insert into test222 values (2, '111', 1, 'boy')
insert into test222 values (3, '1111', 2, 'boo')
insert into test222 values (4, '11111', 2, 'bo')
insert into test222 values (5, '111111', 0, 'boyy')
insert into test222 values (6, '1111111', 5, 'gril')
insert into test222 values (7, '22', 5, 'body')
insert into test222 values (8, '222', 0, 'girll')
insert into test222 values (9, '33', 8, 'boy')
insert into test222 values (10, '333', 9, 'bo')
following is your code,,,
;WITH SInfo AS
(
SELECT
t.sId,
t.scode,
t.ParentId,
t.sName,
CONVERT(nvarchar(800), t.scode) AS Hierarchy,
t.ParentId as HParentId
FROM test222 as t
WHERE
t.sname like '%bo%'
UNION ALL
SELECT
si.sId,
si.scode,
si.ParentId,
si.sName,
CONVERT(nvarchar(800), TH.scode + '\' + si.Hierarchy),
th.parentid
FROM SInfo as si
INNER JOIN test222 TH
ON TH.sId = si.HParentId
)
Select t.sId, t.scode, t.ParentId, t.sName, t.Hierarchy
from SInfo as t
where
HParentId = 0 and
not exists (select 1 from SInfo as s
where
s.sid <> t.sid and
s.Hierarchy like t.Hierarchy + '%')
op expected is as follows
11\111\1111-11111(2 childs)
111111\22
222\33\333
how can i do that???
April 17, 2015 at 4:30 am
can anyone heree pls help me?????????
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply