September 1, 2014 at 12:57 am
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
---- create table
create table #test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))
---- insert records
insert into #test values (1, '11', 0, 'a')
insert into #test values (2, '111', 1, 'b')
insert into #test values (3, '1111', 2, 'c')
insert into #test values (4, '11111', 3, 'd')
---- result query
;WITH SInfo AS
(
SELECT sId
,scode
,ParentId
,sName
,CONVERT(nvarchar(800),(scode+ '-' + scode+ '1')) AS Hierarchy
FROM #test
WHERE ParentId = 0
UNION ALL
SELECT TH.sId
,TH.scode
,TH.ParentId
,TH.sName
,CONVERT(nvarchar(800), (SInfo.Hierarchy +'-' + CONVERT(nvarchar(800), TH.scode) + CONVERT(nvarchar(800), 1)))
FROM #test TH
INNER JOIN SInfo ON SInfo.sId = TH.ParentId
)
Select * from SInfo
September 2, 2014 at 1:18 am
CELKO (9/1/2014)
People don't mind helping with homework but you'll find you're likely to get a better response if you post what you've already tried rather than just asking for the answer.
NOT TRUE ! many of us have been professors and have taken oath to uphold academic honor. If we find a student committing plagiarism in a forum, we have to report them.
Over the years, I have expelled 3 students and 1 teacher (he used my copyrighted material in his class for a homework assignment).
I'm sorry Mr Celko but at what point does plagiarism come in to my post? All I said was 'show us what you've done'. I made no mention of copyrighted material and I fully understand why sanctions have to be taken against those that mis-use it. It seems to be a huge leap though, from suggesting the best way to ask for help to talking about expulsion for plagiarism. My original post is also completely true, I don't mind helping with homework (as far as I can), but I don't want to spoon-feed somebody when they will probably learn more by thinking through a problem themselves with assistance from others.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 3, 2014 at 8:41 am
What version of SQL Server are you using?
If you use a recursive CTE to traverse the tree then you can create the path from the root to specific node and then in the outer query use the offset window function LEAD to show the path of the next node in the sequence.
WITH Tree AS (
SELECT
student_id,
student_code,
student_parent_id,
student_name,
CAST(student_code AS varchar(MAX)) AS hierarchy,
CAST(student_id AS varbinary(900)) AS SortOrder
FROM
dbo.Student
WHERE
student_parent_id IS NULL
UNION ALL
SELECT
C.student_id,
C.student_code,
C.student_parent_id,
C.student_name,
P.hierarchy + '-' + C.student_code,
CAST(P.SortOrder + CAST(ROW_NUMBER() OVER(PARTITION BY C.student_parent_id ORDER BY C.student_id) AS binary(4)) AS varbinary(900))
FROM
Tree AS P
INNER JOIN
dbo.Student AS C
ON C.student_parent_id = P.student_id
)
SELECT
student_id,
student_code,
student_parent_id,
student_name,
hierarchy,
LEAD(hierarchy, 1, student_code) OVER(ORDER BY SortOrder) AS lead_hierarchy
FROM
Tree
ORDER BY
SortOrder;
GO
I am using the presentation order as the ordering subclause for the offset function since you did not provide any clue in the presence of siblings.
What should be the expected result if we add the following rows?
(5, '22', NULL, 'whatever-22'),
(6, '222', 5, 'whatever-222');
Should the output of the hierarchy for [student_id] = 4 be '11111' or '22'?
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply