2

Given the following schema and data:

CREATE TABLE ##Nodes
(
    NodeCode hierarchyid,
    NodeName varchar(10)

)

INSERT INTO ##Nodes VALUES ('/','RootNode')
INSERT INTO ##Nodes VALUES ('/1/','Node1')
INSERT INTO ##Nodes VALUES ('/1/1/','Node1.1')
INSERT INTO ##Nodes VALUES ('/2/','Node2')
INSERT INTO ##Nodes VALUES ('/3/','Node3')
INSERT INTO ##Nodes VALUES ('/3/1/','Node3.1')
INSERT INTO ##Nodes VALUES ('/4/','Node4')

How can I get the following result:

NodeCode | NodeName | HasDescendants
/, RootNode, true
/1/, Node1, true,
/1/1/, Node1.1, false
/2/, Node2, false
/3/, Node3, true
/3/1/, Node3.1, false
/4/, Node4, false
B Z
  • 9,363
  • 16
  • 67
  • 91

2 Answers2

3
SELECT NodeCode.ToString() AS NodeCode, NodeName,     
       CASE WHEN (SELECT TOP 1 n.NodeCode 
                  FROM ##Nodes 
                  WHERE NodeCode.GetAncestor(1) = n.NodeCode) IS NULL THEN 'false'
            ELSE 'true' END AS HasDescendants       
FROM ##Nodes n

Demo on SQLFiddle

Aleksandr Fedorenko
  • 16,594
  • 6
  • 37
  • 44
  • To add to this, or at least in my case, I found adding an ORDER BY HierarchyNode ASC to the CASE WHEN sub select query improves performance massively. – RobHurd May 22 '14 at 21:53
0

Updated to produce a correct result:

SELECT DISTINCT
    n1.NodeCode.ToString()
   ,n1.NodeName
   ,(CASE WHEN n2.NodeCode IS NULL THEN 'false' ELSE 'true' END)
FROM 
    Nodes n1 
LEFT OUTER JOIN 
    Nodes n2 
    ON 
    n1.NodeCode = n2.NodeCode.GetAncestor(1)

This is pretty much a straight copy of the answer I found here.

Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • Thanks @Cory, but this doesn't give the same result. It repeats the root node 4 times. – B Z Jan 18 '13 at 15:51
  • @BZ: Correct you are. I updated the the query to produce a correct result, so future visitors aren't misled! Unfortunately this is not quite as elegant as Alexander's answer O_o – Cᴏʀʏ Jan 18 '13 at 16:07