Stack Overflow
Stack Overflow

Questions tagged [hierarchyid]

hierarchyid is a TSQL extension available from SQL Server 2008 to map hierarchical structures in a flat table. It supports in-order-sorting, get-descendant and level-queries

hierarchyid is a TSQL extension available from SQL Server 2008 to map hierarchical structures in a flat table. It supports in-order-sorting, get-descendant and level-queries

152 questions
1
vote
1 answer

HierarchyID with .NET 5 and EF Core throws cast error

I'll say straight up at the beginning that I am not a 'real' developer. I'm currently teaching myself C# using a pet project, and I've hit a stumbling block that no amount of Googling or book reviewing has helped thus far. The few people I know with…
Christopher Whitfield
  • 23
  • 3
1
vote
1 answer

Find value contained in the HierarchyId at any level

I need to find a particular value contained in the SQL Server HierarchyId column. The value can occur at any level. Here is a sample code to illustrate the issue: CREATE TABLE mytable ( Id INT NOT NULL PRIMARY KEY, TeamName VARCHAR(20) NOT…
SQL_Guy
  • 333
  • 4
  • 15
1
vote
1 answer

Get Lowest level in tree created with hierarchyId that respect some conditions

I have created a hierarchy table in my SQL Server. I have a column hierarchyId. Each level of hierarchy represent a geographical/political level of a country: Countries Regions Provinces For each row I can fill some boundaries or not. To simplify…
Simone
  • 2,304
  • 6
  • 30
  • 79
1
vote
1 answer

Get all last members in a SQL Server hierarchy

I am trying to find all first items of the last hierarchy members ("leafs") in a SQL Server hierarchy. I was able to find similar questions, however solutions with GetAncestors don't work with our SQL Server version. I was able to use this…
Oak_3260548
  • 1,882
  • 3
  • 23
  • 41
1
vote
1 answer

What is the equivalent datatype for SQL Server hierarchyid in PostgreSQL

I have a table in SQL Server which has a column of type hierarchyid, the issue is that I can't find the equivalent datatype for this in PostgreSQL. CREATE TABLE dbo.exampleTable{ id int; name varchar(255); level hierarchyid not null; } How…
TejusN
  • 77
  • 9
1
vote
0 answers

Working with hierarchyid in Entity Framework Core

I'm working with EF Core 3.1 and i use sql server datatype hierarchyid on one of my tables. When i scaffold the table into my project the 'hierarchyid' turns into Geometry type. I tried manually changing into SqlHierarchyId type with the help of …
Shai Barak
  • 129
  • 8
1
vote
1 answer

Use CLR function for convert Hierarchyid to string in visual studio 2019

Please advise me. I try to write a CLR function for SQL server. I using the Microsoft document for write below function Ref-LINK Finding Ancestors by Using the CLR A common operation involving two nodes in a hierarchy is to find the lowest common…
Amirhossein
  • 1,148
  • 3
  • 15
  • 34
1
vote
2 answers

SQL Server - Get first node from HierarchyId field

Suppose I have the following HIERARCHYID column in my SQL Server table: MyCol /1/1/ /2/1/ /3/1/1/ /3/1/2/ /3/2/1/1/ /3/2/1/2/ /4/1/ /4/2/ /5/1/ /6/1/ /7/1/ /8/1/ /8/2/ And I want to write a query that returns…
John Bustos
  • 19,036
  • 17
  • 89
  • 151
1
vote
1 answer

How to get recursivelevel using SQL Server 2012 hierarchyid?

I know its quite challenging, Is there any sql specialist please kindly help me to solve this. I Have hierarchyID like below in NameHID column. Its representing /NameID/dadID/MomID/ respectively. Which means the students fatherid and the motherID.…
Manojkanth
  • 1,071
  • 11
  • 27
1
vote
0 answers

SQLserver hierarchyid data type in Java

I am new to SQLserver. I have a table with hierarchyid as the primary key . When I generated the entity for the table through hibernate reverse engineering the column which had hierarchyid as the datatype has become byte[] in the java entity file.…
Artist
  • 93
  • 1
  • 10
1
vote
3 answers

Using SQL HierarchyID to select respectively from Children to Parents

I need to write a Stored Procedure to return some data on an order from children to parents.It is somehow complicated to describe what I want to do, but let me try it: Imagine we have this Hierarchy called Categories: Parent > Child1 > Child2 >…
Yasin
  • 51
  • 11
1
vote
0 answers

Converting dataset with HIERARCHYID to json in that hierarchy

I have a table in which columns1 is of type hierarchyID and when you do Select Convert(varchar,Column1), Name from Table1 The result is /1/ Root /1/1/ Child 1 /1/2/ Child 2 /1/3/ Child 3 /1/2/1/ Child 2 Child 1 /1/2/2/…
user1558076
  • 45
  • 5
1
vote
1 answer

Updating "Hierarchyid" in SQL Server

I've used Hierarchyid data type in one of my tables in SQL Server. Now I want to change the father of one of the rows, but when I change that all its descendant HierarchyId's must change according to that. Is there a function to do that or I must…
Scarlet
  • 271
  • 2
  • 12
1
vote
2 answers

SQL Server 2012 IsDescendantOf SLOW

Let's say that the table 'Items' (about 1000 records) has the following fields: ItemID (smallint, primary key) ItemHierarchyID (HierarchyID) ItemName (varchar(max)) This query (just for testing purposes) takes about 0 seconds: SELECT A.* …
Rick
  • 1,042
  • 2
  • 14
  • 34
1
vote
2 answers

Convert a string to a hierarchyid in C#

I need to be able to convert a string to a hierarchyid in c#.net - I cannot use stored procedures. When I pass in the path (string) the query fails as the path is stored like this '/' instead of / Can I convert it to another type? SqlCommand command…
ClareBear
  • 1,493
  • 6
  • 25
  • 47
1 2 3
…
10 11