Forum Discussion
HierarchyID display
Not sure what a "standard Microsoft hierarchyID column positioning parts" means. Can you show some example/sample data?
I am likely way off, but Power BI has PATH, PATHITEM, etc. functions to deal with parent-child values.
OK.
Rather than use my daya - I'll use teh data from the HumanResources.Employee table in SQL 2016
SELECT E.BusinessEntityID, P.FirstName + ' ' + P.LastName as 'Name',
OrganizationNode, OrganizationNode.ToString() as 'HierarchyID.ToString()',
OrganizationLevel
FROM HumanResources.Employee E
JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID
order by OrganizationNodeThat will produce a listing of employees sorted by OrganizationalNode You can see the relationship between lines through the ToString() column. I would like to display the data in the correct hierarchy order, with the hierarchys offset dependant on level.
Does this help?
Not entirely, what would an actual row of data look like? I don't need your data, just a representation of a data row.
Run this
DECLARE @tab CHAR(2)
SET @tab = CHAR(9)SELECT
Convert(nvarchar(100),REPLICATE(@tab , b.OrganizationNode.GetLevel()) )
+ b.LoginID + '--- '
+ Convert(nvarchar(25),b.OrganizationNode.GetLevel() )
as listing
FROM [HumanResources].[Employee] AS b
order by OrganizationNode#
The output is a single column - with a number of tabs before teh text. If you output to file and load into Excel the tabs will spread the text across the XLS, one column of each tab.
The raw data is exactly as you'll see if you do a select * from AdventureWorks2014.HumanResources.Employee
