Hierarchy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hierarchy

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2011-07-18 : 05:32:43

Hello All,

I been given some data from a salesforce and have it stored locally in a sql 2005 db. The table is as follows. Its only max of two level hierarchy. I just want to be able to display it with the child nodes indented from a select query. Any help would be great.

TABLE Hierarchy
ParentId NCHAR(18)
AccountId NCHAR(18)
NAME nvarchar 255)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-18 : 05:36:30

select ParentID, AccountID, CASE WHEN ParentID IS NULL THEN Name ELSE ' ' + Name from dbo.Hierarchy

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 05:37:47

if only max of 2 level, self join the table


select a.ParentID, Parent_Name = p.Name, a.AccountID, a.Name
from   tbl a
       inner join tbl p on a.ParentID = p.AccountID

KH
[spoiler]Time is always against us[/spoiler]

lemondash
Posting Yak Master

159 Posts

Posted - 2011-07-18 : 05:48:20

Thanks for the suggests so far, i tried the self join but really was trying to do away with that because i wanted to indent the child for teh display and it repeats the parent for every row.

apologies.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 05:55:11

quote:
Originally posted by lemondash

Thanks for the suggests so far, i tried the self join but really was trying to do away with that because i wanted to indent the child for teh display and it repeats the parent for every row.

apologies.

Shouldn't the displaying of data the job of your front end application and not SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

lemondash
Posting Yak Master

159 Posts

Posted - 2011-07-18 : 06:10:55

Thats the problem its a salesforce application, so it looks all nice in the application. But there are some data issues and need it display indented in other format.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-18 : 06:19:13

Please post some sample data and expected output.

N 56°04'39.26"
E 12°55'05.63"

lemondash
Posting Yak Master

159 Posts

Posted - 2011-07-18 : 06:31:18

Name Id ParentId
Here is the sample data in the table.

Name [ID] [ParentId]
3i PLC BIRMINGHAM 0012000000T0I0fAAF 0012000000QIyCWAA1
3i PLC GROUP 0012000000QIyCWAA1 NULL
4R Business Recovery 0012000000kky2kAAA NULL
7 A 0012000000Qp5R7AAJ 0012000000Qp4M8AAJ
7 A Business) 0012000000Qp4M8AAJ NULL
7Side CARDIFF 0012000000T0O81AAF 0012000000T0O7SAAV
7Side GROUP 0012000000T0O7SAAV NULL

Here is how i would like the output.

3i PLC GROUP
3i PLC BIRMINGHAM(indent/tabbed)

Hour 4R Business RECOVERY

7 A Business (New Group)
7 A (New Invoice Point)(indent/tabbed)

5 Side GROUP
5 Side CARDIFF(indent/tabbed)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources