Hierarchial Query

Question

Hierarchial Query

peterausger

Mr or Mrs. 500

Points: 544
More actions
August 29, 2014 at 2:16 am

#311154

Dear all
Following is my db table
student_id student_code student_parent_id student_name
1 11 0 a
2 111 1 b
3 1111 2 c
4 11111 3 d
Can anyone here please help me to generate following op?
student_id student_code student_parent_id student_name 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
Thanks
Peter

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23000 More actions · Answer 1

Hi Peter,

welcome to the forums. If you have a read of this [/url] article, it'll make it much easier to see what you want. All those 1's and dashes make it very difficult to see where you columns are.

If I may say, what you're asking looks suspiciously like homework. 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.

Neil

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 http://www.sqlservercentral.com/articles/Best+Practices/61537

peterausger Mr or Mrs. 500 Points: 544 More actions · Answer 2

Hello there,

I tried different options but not getting exactly what i am looking for..

Kindly refer the attachment for the expected op

Thanks

Peter

Neil Burton SSC-Insane Points: 23000 More actions · Answer 3

Thanks Peter. Could you post the code you've tried please?

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 http://www.sqlservercentral.com/articles/Best+Practices/61537

peterausger Mr or Mrs. 500 Points: 544 More actions · Answer 4

this is what i tried and not getting the expected op

;WITH SInfo AS

(

SELECTsId

,scode

,ParentId

,sName

,CONVERT(nvarchar(800), scode) AS Hierarchy

FROMtest

WHEREParentId=0

UNIONALL

SELECTTH.sId

,TH.scode

,TH.ParentId

,TH.sName

,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))

FROMtest TH

INNER JOINSInfoONSInfo.sId = TH.ParentId

)

Select * from SInfo

thanks

peter

Neil Burton SSC-Insane Points: 23000 More actions · Answer 5

Thanks again.

Next question 🙂 Can you post your table definitions please? There are some column names in the code that don't match what you originally posted. And, looking at your code there's a backslash in there that doesn't appear in your expected outcome. I'm starting to see what you're trying to achieve but I don't want to make any assumptions and get it wrong.

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 http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

peterausger (8/29/2014)
this is what i tried and not getting the expected op
;WITH SInfo AS
(
SELECTsId
,scode
,ParentId
,sName
,CONVERT(nvarchar(800), scode) AS Hierarchy
FROMtest
WHEREParentId=0
UNIONALL
SELECTTH.sId
,TH.scode
,TH.ParentId
,TH.sName
,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))
FROMtest TH
INNER JOINSInfoONSInfo.sId = TH.ParentId
)
Select * from SInfo
thanks
peter

Peter,

Post the data and the table as recommended in the first link under "helpful" links in my signature line below and I'm sure that someone will be able to answer your question in two shakes.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

peterausger Mr or Mrs. 500 Points: 544 More actions · Answer 7

Kindly find attached the table and the op required..

Hope this info is good enough...

Thanks

Peter

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

peterausger (8/29/2014)
Kindly find attached the table and the op required..
Hope this info is good enough...
Thanks
Peter

Graphics are copyable/pastable to SSMS. Do like the article says. Make the code and data readily consumable. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

peterausger Mr or Mrs. 500 Points: 544 More actions · Answer 9

---- 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

(

SELECTsId

,scode

,ParentId

,sName

,CONVERT(nvarchar(800), scode) AS Hierarchy

FROMtest

WHEREParentId=0

UNIONALL

SELECTTH.sId

,TH.scode

,TH.ParentId

,TH.sName

,CONVERT(nvarchar(800), (SInfo.Hierarchy +'\' + CONVERT(nvarchar(800), TH.scode)))

FROMtest TH

INNER JOINSInfoONSInfo.sId = TH.ParentId

)

Select * from SInfo

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 14

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