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 ««123»»

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: Today @ 8:27 AM
Points: 35,349, Visits: 31,889
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: Today @ 8:27 AM
Points: 35,349, Visits: 31,889
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: Today @ 8:27 AM
Points: 35,349, Visits: 31,889
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 (14 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: Today @ 8:27 AM
Points: 35,349, Visits: 31,889
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:36 PM
Points: 2,201, Visits: 5,944
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 Monday, September 1, 2014 3:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 1,945, Visits: 3,064
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).


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1609402
Posted Monday, September 1, 2014 3:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 1,945, Visits: 3,064
Following is my db table


NO, this is useless ASCII pictures. Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. Think! Can you program from pictures? How?


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1609403
Posted Monday, September 1, 2014 4:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 1,945, Visits: 3,064
No, Peter, you still got it wrong. Please post correct DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

You do even know what a key is! Everything in your world is NULL-able! The term “parent” is from network databases. Rows are not records. You do not know how to write INSERT INTO in standard SQL. Why are you using BIGINT for identifiers? What math do you do on them? Why do you CONVERT (a 1970's Sybase function) and not CAST?

I also loved seeing the leading commas! We did that with punch cards! It made it easy to re-uses the cards in the 1960's. Where did you learn that?

This looks like an adjacency list model. SQL Programmers use nested sets, and not fake pointer chains.

Based on doing this for a few decades and some expertise, I will guess you are 3 to 5 years away from being an employable SQL programmer. Can you take the time to get an education?

If you want to kludge things for now, get a copy of TREES & HIERARCHIES IN SQL and look at the nested sets model.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1609404
Posted Tuesday, September 2, 2014 1:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 1,107, Visits: 3,556
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
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse