Simple Query Hierarchical data

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

SQL Server 2005 Forums

Transact-SQL (2005)

Simple Query Hierarchical data

Author

Topic

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-05-16 : 15:22:02

I have data that is hierarical.

Declare  @foo table
(
ID int,MemberName nvarchar(255),ParentID int
)

Insert into @foo

select 8,'Legacy Application Support',4 union all
select 4,'Information Technology',3 union all
select 1,'Contoso Corporation',null union all
select 2,'Sales and Marketing',1 union all
select 5,'Store Operations',3 union all
select 6,'Application Development',4 union all
select 7,'Infrastructure Support',4 union all
select 3,'Operations',1

Is it possible to return the following?


1 Contoso Corporation	null
2   Sales and Marketing	1
3   Operations	1
5      Store Operations	3
4      Information Technology	3
6         Application Development	4
7         Infrastructure Support	4
8	  Legacy Application Support	4

Nick W Saban

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-16 : 15:47:25

Try something like this:


WITH TreeCTE AS
(
  SELECT ID, MemberName, ParentID, 0 AS level
    FROM @foo
   WHERE ParentID IS NULL

  UNION ALL
  
  SELECT Curr.ID, Curr.MemberName, Curr.ParentID, Prev.level + 1
    FROM TreeCTE AS Prev
         INNER JOIN
         @foo AS Curr
         ON Curr.ParentID = Prev.ID
)
SELECT ID, REPLICATE('  ', level) + MemberName AS MemberName, ParentID
  FROM TreeCTE
 ORDER BY level;

In your required result I could not figure out the columns you used to get the result in that order except the level. So I assumed that the order is based on level only (and when equals order the rows arbitrary ).

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-05-16 : 15:53:44

Thanks!! I'll try it now.

Nick W Saban

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-05-16 : 15:55:53

Works like a charm! thank you Malpashaa very much!

Nick W Saban

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-05-16 : 16:25:42

Spoke too soon..I'm kinda new to CTE's. the Information Technology department has 3 children - which are not appearing immediately below since you are ordering on level.

Is it possible to list all children for each department directly underneath?

Declare  @foo table
(
ID int,MemberName nvarchar(255),ParentID int
)

Insert into @foo

select 8,'Legacy Application Support',4 union all
select 4,'Information Technology',3 union all
select 1,'Contoso Corporation',null union all
select 2,'Sales and Marketing',1 union all
select 5,'Store Operations',3 union all
select 6,'Application Development',4 union all
select 7,'Infrastructure Support',4 union all
select 3,'Operations',1

;WITH TreeCTE AS
(
  SELECT ID, MemberName, ParentID, 0 AS level
    FROM @foo
   WHERE ParentID IS NULL

  UNION ALL
  
  SELECT Curr.ID, Curr.MemberName, Curr.ParentID, Prev.level + 1
    FROM TreeCTE AS Prev
         INNER JOIN
         @foo AS Curr
         ON Curr.ParentID = Prev.ID
)
SELECT ID, REPLICATE('  ', level) + MemberName AS MemberName, ParentID
  FROM TreeCTE
 ORDER BY level;

Results would be like:

1 Contoso Corporation	null
2   Sales and Marketing	1
3   Operations	1
5      Store Operations	3
4      Information Technology	3
6         Application Development	4
7         Infrastructure Support	4
8	  Legacy Application Support	4

Nick W Saban

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-16 : 17:30:25

Yes, it is possible. Try this:


WITH TreeCTE AS
(
  SELECT ID, MemberName, ParentID, 0 AS level, CAST(1 AS VARBINARY(MAX)) AS sort_path
    FROM @foo
   WHERE ParentID IS NULL

  UNION ALL
  
  SELECT Curr.ID, Curr.MemberName, Curr.ParentID, Prev.level + 1,
         Prev.sort_path + CAST(ROW_NUMBER() OVER(PARTITION BY Curr.ParentID ORDER BY (SELECT NULL)) AS BINARY(4))
    FROM TreeCTE AS Prev
         INNER JOIN
         @foo AS Curr
         ON Curr.ParentID = Prev.ID
)
SELECT ID, REPLICATE('  ', level) + MemberName AS MemberName, ParentID
  FROM TreeCTE
 ORDER BY sort_path;

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-05-16 : 20:08:22

That did it. Thanks!! Very impressive solution!

Nick W Saban

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-05-16 : 21:06:12

Oh crap... i tried it on a different dataset and it does not properly display the parent child relationships. Can you take a look?? I'm very grateful for your help.



Declare  @foo table
(
ID int,MemberName nvarchar(255),ParentID int
)

Insert into @foo


select 1,'Gulf Range Instrumentation',null union all
select 2,'GR Airborne Platform', 1 union all
select 3, 'C-17 Systems',null union all
select 4,'GR Pods',1 union all
select 5,'C-17 Rollers',3 union all
select 6,'C-17 Straps',3 union all
select 7, 'GR Bouy Instumentation',1


;WITH TreeCTE AS
(
  SELECT ID, MemberName, ParentID, 0 AS level, CAST(1 AS VARBINARY(MAX)) AS sort_path
    FROM @foo
   WHERE ParentID IS NULL

  UNION ALL
  
  SELECT Curr.ID, Curr.MemberName, Curr.ParentID, Prev.level + 4,
         Prev.sort_path + CAST(ROW_NUMBER() OVER(PARTITION BY Curr.ParentID ORDER BY (SELECT NULL)) AS BINARY(4))
    FROM TreeCTE AS Prev
         INNER JOIN
         @foo AS Curr
         ON Curr.ParentID = Prev.ID
)
SELECT ID, REPLICATE('  ', level) + MemberName AS MemberName, ParentID
  FROM TreeCTE
 ORDER BY sort_path;

Nick W Saban

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-05-16 : 21:07:25

This data set has 2 elements with no parent.

Nick W Saban

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-17 : 03:08:10

You just need to replace 1 with ID like this:


/* Old */
CAST(1 AS VARBINARY(MAX))

/* New */
CAST(ID AS VARBINARY(MAX))

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-05-17 : 07:18:57

That did it. Thanks SO MUCH Malpashaa!

Nick W Saban

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources