Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Hierarchial Query Expand / Collapse
Author
Message
Posted Friday, August 29, 2014 10:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 38,341, Visits: 35,263
Crud. I have the answer but my connection from work won't let me post it. I'll see if I can attach it as a file.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1608682
Posted Friday, August 29, 2014 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 38,341, Visits: 35,263
First, here's the result set from my solution for this problem...
Results:

sId scode ParentID sName Hierarchy
--- ----- -------- ----- -------------------
1 11 0 a 11\111
2 111 1 b 11\111\1111
3 1111 2 c 11\111\1111\11111
4 11111 3 d 11111

(4 row(s) affected)



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1608685
Posted Friday, August 29, 2014 10:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 38,341, Visits: 35,263
See attached for the SQL Code.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems


  Post Attachments 
Hierarchy.txt (16 views, 716 bytes)
Post #1608688
Posted Friday, August 29, 2014 10:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 38,341, Visits: 35,263
Ok... your turn. Why did you need to have the Hierarchy column so that each row contained the next child and the leaf levels of the hierarchy only contained the child ID? Why the deviation from the normal expanded hierarchical order?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1608689
Posted Sunday, August 31, 2014 4:25 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:22 PM
Points: 4,375, Visits: 11,257
This is more for fun than being an attempt to solve the problem, although the code can easily be amended to produce the desired results. It uses an inline Tally table and FOR XML to build the Hierarchy path.


USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.test') IS NULL
BEGIN
---- create table
create table dbo.test(sid bigint, scode nvarchar(50), parentid bigint, sname nvarchar(50))

---- insert records
insert into dbo.test values
(1, '11', 0, 'a')
,(2, '111', 1, 'b')
,(3, '1111', 2, 'c')
,(4, '11111', 3, 'd');
END
--SET STATISTICS IO ON;
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
--sId scode ParentID sName Hierarchy
SELECT
T.sid
,T.scode
,T.parentid
,T.sname
,(( SELECT
TP.scode + '\'
FROM dbo.test TT
OUTER APPLY
(
SELECT TOP((TT.parentid) + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS N FROM
T T1,T T2,T T3,T T4,T T5,T T6,T T7
) AS NM(N)
LEFT OUTER JOIN dbo.test TP
ON NM.N = TP.sid
WHERE T.sid = TT.sid
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(MAX)') + T.scode
) AS Hierarchy
FROM dbo.test T
--SET STATISTICS IO OFF;

Results
sid  scode  parentid  sname  Hierarchy
---- ------ --------- ------ ------------------
1 11 0 a 11
2 111 1 b 11\111
3 1111 2 c 11\111\1111
4 11111 3 d 11\111\1111\11111
Post #1609098
Posted Monday, September 1, 2014 12:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:38 AM
Points: 10, Visits: 32
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
Post #1609247
Posted Tuesday, September 2, 2014 1:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 1,654, Visits: 5,950
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.



On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help
Post #1609469
Posted Wednesday, September 3, 2014 8:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 30, 2014 12:54 PM
Points: 115, Visits: 639
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'?



Post #1610078
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse