All Questions
Tagged with hierarchy sql-server
45 questions
0
votes
1
answer
86
views
Normalisation with an Underlying Hierarchy
I'm designing a new database schema, and every time I do, I like to check my assumptions on normalisation. Something I haven't been able to find a definitive answer for here or by searching the web is ...
- 117
1
vote
0
answers
214
views
How to write query to link complex Parent-Child relationships
We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.
I need to link these fields together by creating a ...
- 11
-1
votes
1
answer
130
views
Creating a hierarchy from text
I'm trying to create a hierarchy from a text field I have in my table.
Visually it is relatively easy to see who is parent and child.
For example: (see a screenshot of the table results)
1M
-----1M5
--...
- 1
0
votes
1
answer
37
views
sql query for category heirarchy validations
I need to add validation on category creation.
CASE 1: parentId should be valid if supplied
CASE 2: name of sibling could not be duplicated
I have this table: (categories)
id | parentId | name
...
- 103
2
votes
1
answer
59
views
Is there any harm in storing other rows' int PKs in a hierarchyID instead of an arbitrary number?
I'm looking at common uses of hierarchyID, and most examples use some sort of arbitrary numbering for the HID nodes:
HID, Name
/, World
/1/, USA
/1/1/, Texas
/1/1/1/, Houston
/1/1/2/, San Antonio
/1/2/...
- 53
1
vote
1
answer
104
views
What facilities of SQLServer would help achieve a goal of tracking a hierarchy over time?
I've a requirement to create a system that tracks what is essentially a hierarchy of containers over a period of time, modelling a supply chain. Everything in the chain is a container, so the process ...
- 53
2
votes
0
answers
139
views
MS-SQL and HierarchyID
I have been using HierarchyID for the last 2 years in some of my tables for obvious purposes
And I have kept a Column (int) for the last node number
for example if I have these rows so the int column ...
- 119
0
votes
1
answer
174
views
MSSQL Merge 3 hierarchy tables
I want to merge 3 hierarchy tables into one table.
The 3 tables:
CREATE TABLE dbo.tbl1 (
key1 varchar(50) NULL,
key2 varchar(50) NULL
) ON PRIMARY
CREATE TABLE dbo.tbl2 (
key2 varchar(50) ...
2
votes
1
answer
124
views
Org chart CTE with exact index but plan shows expensive sort node
I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it. Names are changed, but it's the same idea. I was thinking of adding a hierarchyid column to ...
- 255
0
votes
1
answer
956
views
Pattern for copying hierarchical data from one table to another?
Imagine a table with three columns: an ID primary key, a nullable ParentID referencing the ID column on that same table, and a TreeID to indicating the records that make up a particular tree. A basic ...
- 121
-1
votes
1
answer
316
views
MS SQL 2017: hierarchy tree - get quantity of last level components
There's a table of items with their components. Components depth vary i.e. some items may have one level of components other may have tens nested components. I need to get quantinty of last level ...
- 303
1
vote
1
answer
236
views
Database Design - hierarchical structure
We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure.
We generate XML files from the database, which our ...
- 11
1
vote
0
answers
412
views
Worked example of closure tables with from and thru dates
I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now.
...
- 11
3
votes
2
answers
5k
views
SQL Server CTE Bottom to Top Recursive with Where clause
I have an Employee Table with an EmployeeId, ManagerId and a Name field.
The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).
I found ...
- 133
10
votes
3
answers
27k
views
Parent/Child Relationship Table Design - What's The Best Practice?
I have a single table for storing 'Tasks'. A task can be a parent and/or a child.
I use the 'ParentID' as the FK referencing the PK on the same table. It is NULLABLE, so if it is NULL it does not have ...
- 533
3
votes
1
answer
217
views
How to store hierarchical dimension for timeseries data
I have a requirement to store data about a load of Searches. Each time someone searches we want to record it for analyzing later on.
As part of the search the user can select one or more Categories ...
- 728
5
votes
2
answers
50k
views
Parent Child Relation in same Table
I have a table storing parent/child records as such:
+-------+------------+---------+---------+------------+-----------+
|custid | custname | deptid | company |parentcustid| enrolled |
+=======+==...
- 435
2
votes
0
answers
679
views
Auto-update hierarchyid of multiple records in INSTEAD OF UPDATE trigger
I am working with an INSTEAD OF UPDATE trigger, where I have to calculate / re-calculate the hierarchyid of a row based on another column value. Thus far, I have it working. The problem that I run ...
3
votes
2
answers
4k
views
Get Nested/Hierarchical Data from SQL Table
I have a table relationship between 'Tag' and 'Task'. It is M:M. The relationship is mapped in the table 'TagTaskMapping'.
The TagAssignment table stores the relationship between a Tag and a date. So ...
- 1,414
5
votes
2
answers
368
views
Find missing folders paths in split hierarchies
I have a table which contains folders' paths. This table contains four columns:
DirID - The folder's ID.
BaseDirID - The ID of the first folder in the hierarchy. So all the folders (paths) from the ...
- 153
3
votes
2
answers
14k
views
How to flatten and pivot a normalised hierarchy table
I have an OrgChart table that looks something like this:
PositionCode PositionName ParentCode Level
AA0001 CEO 0
AA0002 CFO AA0001 1
AA0003 ...
- 31
-1
votes
3
answers
2k
views
Create parent node and parent id [closed]
I have a table which contains id, nodeid, nodevalue, and nodelevel columns.
Now I want to create new temp column ParentnodeId.
I tried using CTE but not getting the correct result.
Id Nodeid ...
- 3
2
votes
1
answer
2k
views
Unflatten hierarchical data in SQL Server
I have some tables in the staging area of a data warehouse that I'm filling with data from some flat, comma-delimited text extracts from another system. When the data comes in the hierarchy of parents ...
- 2,087
1
vote
1
answer
1k
views
How to get last level of a hierarchical list, group by some field
Given the schema:
create table containers (
id int primary key,
name text,
product_id int,
lot int,
qty int,
parent_id int);
create table orders_items (
id int,
...
- 23.9k
0
votes
1
answer
818
views
Best way to implement Hierarchical data in SQL Server with multiple parents
How can I implement Hierarchical data where a child has two or more parents?
Something like this
Parent ID Child ID
1 2
1 3
3,2 4
4,1 5
- 101
9
votes
1
answer
3k
views
Hierarchical permissions in a table stored hierarchy
Assuming the following database structure (modifiable if need be) ...
I am looking for a nice way to determine the "effective permissions" for a given user on a given page in a way that ...
- 220
1
vote
1
answer
403
views
Convert hierarchy in an ordered list of rows
I tried to make a simple list that represents hierarchy of items.
Tags have simple structure like
[Id]
,[Name]
,[ParentTagId]
I try to make the follwing result table:
ID, ParentID, Name, Lev, ...
- 241
3
votes
1
answer
3k
views
Create a hierarchy chain (reverse path) for each node in a tree
Given a typical closure table, how would I write a query to get a list of ids back to the root? Is there a way to do that for all the [unique] ids? I'm using Microsoft SQL Server.
Given:
parent ...
- 211
7
votes
2
answers
247
views
Transposing hierachical data from one VARCHAR to two INTs
THE PROBLEM
The table where the data is recorded is
CREATE TABLE [dbo].[Almoxarifado](
[idAlmoxarifado] [varchar](20) NOT NULL,
[tipoAlmoxarifadoId] [varchar](30) NOT NULL,
[entidadeId] [...
- 591
9
votes
3
answers
436
views
Is the hierarchyid CLR open-source?
The built-in hierarchyid is a CLR that stores paths in an efficient binary form, and provides other useful functionality.
Unfortunately, there is limit to how deep the represented paths can be, and ...
- 193
3
votes
2
answers
5k
views
Better defining a parent-child relationship on a table
Our team is developing a new system and it has a Warehouse table that must have a hierarchical relationship between entries since a customer can have 1st, 2nd, 3rd and Nth degree warehouses.
We first ...
- 591
4
votes
1
answer
683
views
How do I make a nullable "parent" hierarchy level?
I'm setting up my first dimensional database with SSAS, and I have this [Materials] dimension that needs a hierarchy that goes something like this:
[PriceCode v] --> nullable
Price Code
...
...
- 914
3
votes
2
answers
813
views
How can I re-index a column using SQL Server Compact Edition?
We have a table TemplateItem that has a column ListIndex that stores the order in which we want the items to show in the UI. This table is self-referencing / hierarchical.
The query requirement is ...
- 131
16
votes
3
answers
13k
views
Create hierarchy of multiple levels where each node has a random number of children
I need to create some test data that involves a hierarchy. I could make it easy and do a couple of CROSS JOINs, but that would give me a structure that is completely uniform / without any variation. ...
- 69.5k
1
vote
2
answers
959
views
Include parent node ID in hierarchical recordset of child rows
I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a ...
- 133
1
vote
2
answers
182
views
How to break down RELATIONAL data into HIERARCHY data structure?
I have a query that returns the following dataset:
RELATIONid MAPid D1id D2id D3id
4999 4999 626 1250 7
5000 5000 626 1250 8
For the next step, I need to bind those ...
- 43
3
votes
1
answer
324
views
A workaround for restoring HierarchyId in SQL server 2014
After installing VS 2015 CTP 4, HierarchyID stopped working in SQL Server 2014. This is the bug listed here.
I am looking for a workaround.
To that end, I have tried reinstalling everything associated ...
- 157
1
vote
1
answer
6k
views
HierarchyID: Get all descendants for a list of parents
I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I ...
- 113
3
votes
2
answers
948
views
Storing overridable hierarchical settings
I've searched the Internet and found the question Pattern(s) about hierarchical settings overwriting on Software Engineering SE. It exactly describes my need, the only difference is that I need to ...
- 229
23
votes
3
answers
87k
views
Parent-Child Tree Hierarchical ORDER
I have to following data in SQL Server 2008 R2. SQLFiddle
Schema:
CREATE TABLE [dbo].[ICFilters](
[ICFilterID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NOT NULL DEFAULT 0,
[FilterDesc]...
- 417
-1
votes
2
answers
23k
views
Return hierarchical data from a self-referencing table (parents, children)
I have the following SQL Server 2008 table and data:
CREATE TABLE dbo.MyTable (Sno int, refid int);
INSERT INTO dbo.MyTable VALUES
(17, 8),
(16, 8),
(15, 7),
(14, 7),
(13, 6),
(12, 6),
...
- 125
6
votes
1
answer
904
views
I need help using hierarchyid datatype and requiring a unique constraint on a varchar field
I want to resolve a bunch of tables that had parent->child->grandchild relationships into a single table using hierarchyid identifiers. Here is what I came up with based on what I had.
CREATE ...
- 653
3
votes
4
answers
4k
views
Faster alternative to scalar UDF with recursion to walk tree hierarchy?
I have a scalar function that traverses a simple parent-child hierarchy of customers to find the ancestor that's in charge of billing. Here's a simplified version of the schema.
CREATE TABLE Customer ...
- 339
11
votes
2
answers
894
views
Is there a set based way to load/read a tree branch with HierarchyId
I am playing around with HierarchyId, and I have not figured out a set based way to do the following:
insert all subtree at once
retrieve all subtree at once
This question is related to my previous ...
- 7,404
22
votes
5
answers
9k
views
Anybody using HierarchyId in production? Is it reliable?
Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
- 7,404