Hierarchy with changing levels
-
Hi,
I have a hierachy that has changing levels which gets imported daily. I have created a stored procedure that creates the table daily based on the levels. I am having an issue with populating that table with the data the way the users need to see it. I have been able to do it using multiple joins.
1. If the hierarchy is 17 levels and if only one value is 3 levels deep than that value has to fill in the
rest of the fields with that same value for the remaining levels.
2. This is based on the level field being 0 for the value
3. The value that is populated into the field is the alias value
Thanks for any help or tips.
USE TempDB;
GO
IF OBJECT_ID('#InitialHierarchy','U') IS NOT NULL
DROP TABLE #InitialHierarchy;
GO
CREATE TABLE #InitialHierarchy
(
[Parent] [nvarchar](255) NULL,
[MemberName] [nvarchar](255) NULL,
[Alias] [nvarchar](255) NULL,
[Generation] [int] NULL,
[Level] [int] NULL
) ON [PRIMARY];
GO
-- Here is some test data
INSERT INTO #InitialHierarchy
(Parent,MemberName,Alias,Generation,[Level])
SELECT 'Org','MGD','A-Alias','2','17' UNION ALL
SELECT 'MGD','B','B-Alias','3','16' UNION ALL
SELECT 'MGD','C','C-Alias','3','7' UNION ALL
SELECT 'MGD','D','D-Alias','3','1' UNION ALL
SELECT 'MGD','E','E-Alias','3','1' UNION ALL
SELECT 'MGD','F','F-Alias','3','0' UNION ALL
SELECT 'E','1-1','1-1-Alias','4','0' UNION ALL
SELECT 'E','1-2','1-2-Alias','4','0' UNION ALL
SELECT 'D','2-1','2-1-Alias','4','0' UNION ALL
SELECT 'D','2-2','2-2-Alias','4','0' UNION ALL
SELECT 'D','2-3','2-3-Alias','4','0' UNION ALL
SELECT 'D','2-4','2-4-Alias','4','0' UNION ALL
SELECT 'D','2-5','2-5-Alias','4','0' UNION ALL
SELECT 'C','J','J-Alias','4','2' UNION ALL
SELECT 'C','I','I-Alias','4','1' UNION ALL
SELECT 'C','K','K-Alias','4','1' UNION ALL
SELECT 'C','H','H-Alias','4','1' UNION ALL
SELECT 'C','G','G-Alias','4','2' UNION ALL
SELECT 'C','M','M-Alias','4','1' UNION ALL
SELECT 'C','3-1','3-1-Alias','4','1' UNION ALL
SELECT 'C','3-2','3-2-Alias','4','4' UNION ALL
SELECT 'C','L','L-Alias','4','1' UNION ALL
SELECT 'C','3-3','3-3-Alias','4','6' UNION ALL
SELECT 'L','4-1','4-1-Alias','5','0' UNION ALL
SELECT 'L','4-2','4-2-Alias','5','0' UNION ALL
SELECT 'L','4-3','4-3-Alias','5','0' UNION ALL
SELECT 'I','5-1','5-1-Alias','5','0' UNION ALL
SELECT 'I','5-2','5-2-Alias','5','0' UNION ALL
SELECT 'I','5-3','5-3-Alias','5','0' UNION ALL
SELECT 'I','5-4','5-4-Alias','5','0' UNION ALL
SELECT 'G','6-1','6-1-Alias','5','0' UNION ALL
SELECT 'G','6-2','6-2-Alias','5','0' UNION ALL
SELECT 'G','6-3','6-3-Alias','5','0' UNION ALL
SELECT 'G','6-4','6-4-Alias','5','0' UNION ALL
SELECT 'G','6-5','6-5-Alias','5','0' UNION ALL
SELECT 'G','6-6','6-6-Alias','5','0' UNION ALL
SELECT 'G','6-7','6-7-Alias','5','0' UNION ALL
SELECT 'G','6-8','6-8-Alias','5','0' UNION ALL
SELECT 'G','6-9','6-9-Alias','5','0' UNION ALL
SELECT 'G','6-10','6-10-Alias','5','0' UNION ALL
SELECT 'G','6-11','6-11-Alias','5','0' UNION ALL
SELECT 'G','6-12','6-12-Alias','5','0' UNION ALL
SELECT 'G','6-13','6-13-Alias','5','0' UNION ALL
SELECT 'G','6-14','6-14-Alias','5','0' UNION ALL
SELECT 'G','6-15','6-15-Alias','5','0' UNION ALL
SELECT 'G','6-16','6-16-Alias','5','0' UNION ALL
SELECT 'G','6-17','6-17-Alias','5','0' UNION ALL
SELECT 'G','6-18','6-18-Alias','5','0' UNION ALL
SELECT 'G','6-19','6-19-Alias','5','0' UNION ALL
SELECT 'G','6-20','6-20-Alias','5','0' UNION ALL
SELECT 'G','6-21','6-21-Alias','5','0' UNION ALL
SELECT 'G','6-22','6-22-Alias','5','0' UNION ALL
SELECT 'G','6-23','6-23-Alias','5','0' UNION ALL
SELECT 'G','6-24','6-24-Alias','5','0' UNION ALL
SELECT 'G','6-25','6-25-Alias','5','0' UNION ALL
SELECT 'G','6-26','6-26-Alias','5','0' UNION ALL
SELECT 'G','6-27','6-27-Alias','5','0' UNION ALL
SELECT 'G','6-28','6-28-Alias','5','0' UNION ALL
SELECT 'G','6-29','6-29-Alias','5','0' UNION ALL
SELECT 'G','6-30','6-30-Alias','5','0' UNION ALL
SELECT 'G','6-31','6-31-Alias','5','1' UNION ALL
SELECT 'G','6-32','6-32-Alias','5','1' UNION ALL
SELECT 'G','6-33','6-33-Alias','5','0' UNION ALL
SELECT 'G','6-34','6-34-Alias','5','0' UNION ALL
SELECT 'G','6-35','6-35-Alias','5','0' UNION ALL
SELECT 'H','7-1','7-1-Alias','5','0' UNION ALL
SELECT 'K','8-1','8-1-Alias','5','0' UNION ALL
SELECT 'J','9-1','9-1-Alias','5','0' UNION ALL
SELECT 'J','9-2','9-2-Alias','5','1' UNION ALL
SELECT 'J','9-3','9-3-Alias','5','0' UNION ALL
SELECT 'J','9-4','9-4-Alias','5','0' UNION ALL
SELECT '9-1','10-1','10-1-Alias','6','0' UNION ALL
SELECT '9-2','10-2','10-2-Alias','6','0' UNION ALL
SELECT '9-2','10-3','10-3-Alias','6','0' UNION ALL
SELECT '9-3','10-4','10-4-Alias','6','0';
GO
DECLARE @Parent VARCHAR(100)
DECLARE @MemberVARCHAR(100)
SET @Parent = 'Org'
SET @Member = 'MGD'
SELECT
a.[Level 1 Name],
a.[Level 2 Name],
a.[Level 3 Name],
a.[Level 4 Name],
a.[Level 5 Name],
a.[Level 6 Name],
a.[Level 7 Name],
a.[Level 8 Name],
a.[Level 9 Name],
a.[Level 10 Name],
a.[Level 11 Name],
a.[Level 12 Name],
a.[Level 13 Name],
a.[Level 14 Name],
a.[Level 15 Name],
a.[Level 16 Name],
a.[Level 17 Name]
FROM(
SELECT
a.Alias [Level 1 Name],
b.Alias [Level 2 Name],
b.Alias [Level 3 Name],
b.Alias [Level 4 Name],
b.Alias [Level 5 Name],
b.Alias [Level 6 Name],
b.Alias [Level 7 Name],
b.Alias [Level 8 Name],
b.Alias [Level 9 Name],
b.Alias [Level 10 Name],
b.Alias [Level 11 Name],
b.Alias [Level 12 Name],
b.Alias [Level 13 Name],
b.Alias [Level 14 Name],
b.Alias [Level 15 Name],
b.Alias [Level 16 Name],
b.Alias [Level 17 Name]
FROM #InitialHierarchy a
JOIN #InitialHierarchy b
ON a.MemberName = b.Parent
AND b.[Level] = 0
WHERE a.Parent = @Parent
AND a.MemberName = @Member
UNION ALL
SELECT
a.Alias [Level 1 Name],
b.Alias [Level 2 Name],
c.Alias [Level 3 Name],
c.Alias [Level 4 Name],
c.Alias [Level 5 Name],
c.Alias [Level 6 Name],
c.Alias [Level 7 Name],
c.Alias [Level 8 Name],
c.Alias [Level 9 Name],
c.Alias [Level 10 Name],
c.Alias [Level 11 Name],
c.Alias [Level 12 Name],
c.Alias [Level 13 Name],
c.Alias [Level 14 Name],
c.Alias [Level 15 Name],
c.Alias [Level 16 Name],
c.Alias [Level 17 Name]
FROM #InitialHierarchy a
JOIN #InitialHierarchy b
ON a.MemberName = b.Parent
JOIN #InitialHierarchy c
ON b.MemberName = c.Parent
AND c.[Level] = 0
WHERE a.Parent = @Parent
AND a.MemberName = @Member
UNION ALL
SELECT
a.Alias [Level 1 Name],
b.Alias [Level 2 Name],
c.Alias [Level 3 Name],
d.Alias [Level 4 Name],
d.Alias [Level 5 Name],
d.Alias [Level 6 Name],
d.Alias [Level 7 Name],
d.Alias [Level 8 Name],
d.Alias [Level 9 Name],
d.Alias [Level 10 Name],
d.Alias [Level 11 Name],
d.Alias [Level 12 Name],
d.Alias [Level 13 Name],
d.Alias [Level 14 Name],
d.Alias [Level 15 Name],
d.Alias [Level 16 Name],
d.Alias [Level 17 Name]
FROM #InitialHierarchy a
JOIN #InitialHierarchy b
ON a.MemberName = b.Parent
JOIN #InitialHierarchy c
ON b.MemberName = c.Parent
JOIN #InitialHierarchy d
ON c.MemberName = d.Parent
AND d.[Level] = 0
WHERE a.Parent = @Parent
AND a.MemberName = @Member
) a
-
glabrecque (2/26/2016)
(...)I have been able to do it using multiple joins.
So what exactly is your question, then?
If you are looking for a way to simplify your query, I believe the below could be a starting point. It does not return the exact same data as your version of the query; I am not sure if that is because I don't understand the problem fully or because your data is not fully consistent. But you can probably work from here - or if not post back and I'll try to hep you further.
SELECT a.Alias AS [Level 1 Name],b.Alias AS [Level 2 Name],
COALESCE(c.Alias, b.Alias) AS [Level 3 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 4 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 5 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 6 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 7 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 8 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 9 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 10 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 11 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 12 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 13 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 14 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 15 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 16 Name],
COALESCE(d.Alias, c.Alias, b.Alias) AS [Level 17 Name]
FROM #InitialHierarchy AS a
INNER JOIN #InitialHierarchy AS b
ON b.Parent = a.MemberName
LEFT JOIN #InitialHierarchy AS c
ON c.Parent = b.MemberName
LEFT JOIN #InitialHierarchy AS d
ON d.Parent = c.MemberName
WHERE a.Parent = @Parent
AND a.MemberName = @Member;
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
I keep looking at the jpg file that represents what you want for the final result and I keep asking myself what you would use that result for. If you could let us in on why you need the result in that format and what you're going to do with that result, there might be a whole lot better way to easily achieve whatever your final grand plan is going to be. There's some pretty neat stuff we can do with Adjacency List hierarchies like the one your test data portrays.
BTW... REALLY nice job on providing the test data. I've already built the code to traverse the hierarchy, build the sort path, and a couple of other goodies for virtually any number of levels you might need. I just want to know what you intend to do with the final output as you've posted it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply