March 9, 2011 at 1:38 am
create table #sample(sno int identity,student_no int, head int,task varchar(50))
insert into #sample (student_no,head,task) values(1,10,'tactical')
insert into #sample (student_no,head,task) values(10,20,'basket')
insert into #sample (student_no,head,task) values(20,40,'aerospace')
insert into #sample (student_no,head,task) values(40,10,'robot')
insert into #sample (student_no,head,task) values(10,40,'tackle')
insert into #sample (student_no,head,task) values(40,60,'trick')
if my input is student_no= 1
it shud show
student-no task
----------- -------
10 basket
20 aerospace
40 robot
10 tackle---> here is tricky thing
40 trick---->here too
using student-no input i have to get next head value and loop through it.
Anyway to acheive it?... any help pls...
i tried CTE..but it doesnt work as duplicated values occurs.... any suggestion pls?
thanks in advance
March 9, 2011 at 6:11 am
I'm not entirely sure I understand the requirement, but here goes..
You are using student_no is your 'pointer' and you wish to output values greater than the value specified.
You could use a stored procedure with a cursor (alter the order by clause to suit your own needs)
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_TestLoop]
@student_no AS Int
WITH EXECUTE AS OWNER
AS
BEGIN
--DECLARE @sno AS Int
DECLARE @head AS Int
DECLARE @task AS VARCHAR(50)
SET NOCOUNT ON;
DECLARE c1 CURSOR FAST_FORWARD FOR
SELECT student_no, head, task
FROM [MyTable]
WHERE student_no > @student_no
ORDER BY sno
OPEN c1 FETCH NEXT FROM c1 INTO @student_no, @head, @task
WHILE @@FETCH_STATUS = 0
BEGIN
--Load table or output HERE
PRINT CAST(@student_no AS VARCHAR(10)) + ', ' + @task
FETCH NEXT FROM c1 INTO @student_no, @head, @task
END
CLOSE c1
DEALLOCATE c1
SET NOCOUNT OFF;
END;
Obviously you need to pass the @student_no Parameter to the SP.
HTH.
March 9, 2011 at 6:16 am
Thanks Derby... but i cant use SP.. just CTE or select - query kind of....thats the problem... 🙁
March 9, 2011 at 6:38 am
BeginnerBug (3/9/2011)
create table #sample(sno int identity,student_no int, head int,task varchar(50))insert into #sample (student_no,head,task) values(1,10,'tactical')
insert into #sample (student_no,head,task) values(10,20,'basket')
insert into #sample (student_no,head,task) values(20,40,'aerospace')
insert into #sample (student_no,head,task) values(40,10,'robot')
insert into #sample (student_no,head,task) values(10,40,'tackle')
insert into #sample (student_no,head,task) values(40,60,'trick')
if my input is student_no= 1
it shud show
student-no task
----------- -------
10 basket
20 aerospace
40 robot
10 tackle---> here is tricky thing
40 trick---->here too
using student-no input i have to get next head value and loop through it.
Anyway to acheive it?... any help pls...
i tried CTE..but it doesnt work as duplicated values occurs.... any suggestion pls?
thanks in advance
I think you are going to have to explain how you arrive at your output.
(I hope you are not replying on the order you insert data into #sample as a table is an UNORDERED set.)
March 9, 2011 at 7:07 am
BeginnerBug (3/9/2011)
create table #sample(sno int identity,student_no int, head int,task varchar(50))insert into #sample (student_no,head,task) values(1,10,'tactical')
insert into #sample (student_no,head,task) values(10,20,'basket')
insert into #sample (student_no,head,task) values(20,40,'aerospace')
insert into #sample (student_no,head,task) values(40,10,'robot')
insert into #sample (student_no,head,task) values(10,40,'tackle')
insert into #sample (student_no,head,task) values(40,60,'trick')
if my input is student_no= 1
it shud show
student-no task
----------- -------
10 basket
20 aerospace
40 robot
10 tackle---> here is tricky thing
40 trick---->here too
using student-no input i have to get next head value and loop through it.
Anyway to acheive it?... any help pls...
i tried CTE..but it doesnt work as duplicated values occurs.... any suggestion pls?
thanks in advance
It's tricky because the hierarchy is cyclic. A recursive CTE works fine if you restrict new rows to those which have a higher ID than the current row, like this:
;WITH rCTE AS (
SELECT sno, student_no, head, task, 0 AS [Level]
FROM #sample
WHERE student_no = 1
UNION ALL
SELECT c.sno, c.student_no, c.head, c.task, p.[level] + 1
FROM #sample c
INNER JOIN rCTE p ON p.head = c.student_no AND p.sno < c.sno
)
SELECT DISTINCT sno, student_no, head, task
FROM rCTE
WHERE [Level] BETWEEN 1 AND 4
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 9, 2011 at 8:19 am
thats works like a charm... thanks a lot chris 🙂
March 9, 2011 at 1:32 pm
BeginnerBug (3/9/2011)
thats works like a charm... thanks a lot chris 🙂
For now it works like a charm. What's going to happen when you need to make a modification where you insert larger numbers up higher on the hierarchy?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2011 at 10:39 pm
Hello Jeff... i implemented it in my coding which has more columns..it works fine.. is there any other way to make it simple?:-)
March 10, 2011 at 8:44 am
BeginnerBug (3/9/2011)
Hello Jeff... i implemented it in my coding which has more columns..it works fine.. is there any other way to make it simple?:-)
Adding more columns isn't the problem I was concerned with. 😉 What I'm worried about is when you add a new Head or Student_ID which is out of the "natural order" of things.
Also, I'm not sure why Student_ID is listed more than once unless you have multiple parents for each student. Most folks try to run a "down only" type of "Directed Acyclic Graph" where each node has one and only one parent as you would find in an org chart rather than a geneology chart. You can still easily determine both downline and uplines using DAGs.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply