SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hierarchial Query


Hierarchial Query

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86574 Visits: 41098
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86574 Visits: 41098
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86574 Visits: 41098
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Attachments
Hierarchy.txt (19 views, 716 bytes)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86574 Visits: 41098
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15198 Visits: 18606
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.
Cool

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

sunitha.yanagandala
sunitha.yanagandala
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
Neil Burton
Neil Burton
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3417 Visits: 10177
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search