Hierarchy Path with Levels That Is Reversed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Hierarchy Path with Levels That Is Reversed

Author

Topic

secuchalan
Starting Member

19 Posts

Posted - 2012-04-27 : 03:37:50

I am trying to get the hierarchy level(s) of a classification using an already specified database hierarchy level. In addition to that, I’d like to provide a column that will store the full classification hierarchal path of any classification separated by a delimiter such as “/”.

I’m looking at two tables namely:
1) class structure table which identifies the parent of the classification by classtructureID
2) class ancestor table which already identifies the hierarchy levels per classstructureID (in reverse or descending order)

My goal is to be able to provide the full path (from parent to child) of a classstructureID that is possibly 4 levels deep.

For example: HARDWARE 33C1 MAINTENANCE FOR OTHER WITH ClassstructureID = 1034

Level 1: HW
Level 2: 3CC1
Level 3: MAINTENANCE
Level 4: OTHER

Full Classification Path HW\3CC1\MAINTENANCE\OTHER


--Populate test data
DECLARE @CLASSSTRUCTURE
TABLE (CSID INT, CLASSID VARCHAR(20), PARENT INT, DESCR VARCHAR(200))
INSERT INTO @CLASSSTRUCTURE (CSID, CLASSID, PARENT, DESCR)
SELECT 1002,'HW',NULL,'Hardware' UNION ALL
SELECT 1029,'3CC1',1002,'3CC1' UNION ALL
SELECT 1030,'ACCESS',1029,'Access' UNION ALL
SELECT 14963,'BUILD',1029,'Build' UNION ALL
SELECT 14964,'CODECHANGE',1029,'Code Change' UNION ALL
SELECT 14965,'DBUPDATE',1029,'Database Update' UNION ALL
SELECT 1032,'INSTALL',1029,'Install' UNION ALL
SELECT 1033,'MAINTENANCE',1029,'Maintenance' UNION ALL
SELECT 1034,'OTHER',1033,'Other Maint' 

--Populate test data
DECLARE @CLASSANCESTOR
TABLE (CSID INT, CLASSID VARCHAR(20), ANCESTORCSID INT, ANCESORTCLASSID VARCHAR(200), HIERARCHYLEVELS INT)
INSERT INTO @CLASSANCESTOR (CSID, CLASSID, ANCESTORCSID, ANCESORTCLASSID,HIERARCHYLEVELS)
SELECT 1001,'SW',1001,'SW',0 UNION ALL
SELECT 1002,'HW',1002,'HW',0 UNION ALL
SELECT 1029,'3CC1',1002,'HW',1 UNION ALL
SELECT 1029,'3CC1',1029,'3CC1',0 UNION ALL
SELECT 1030,'ACCESS',1002,'HW',2 UNION ALL
SELECT 1030,'ACCESS',1029,'3CC1',1 UNION ALL
SELECT 1030,'ACCESS',1030,'ACCESS',0 UNION ALL
SELECT 1032,'INSTALL',1002,'HW',2 UNION ALL
SELECT 1032,'INSTALL',1029,'3CC1',1 UNION ALL
SELECT 1032,'INSTALL',1032,'INSTALL',0 UNION ALL
SELECT 1033,'MAINTENANCE',1002,'HW',2 UNION ALL
SELECT 1033,'MAINTENANCE',1029,'3CC1',1 UNION ALL
SELECT 1033,'MAINTENANCE',1033,'MAINTENANCE',0 UNION ALL
SELECT 14963,'BUILD',1002,'HW',2 UNION ALL
SELECT 14963,'BUILD',1029,'3CC1',1 UNION ALL
SELECT 14963,'BUILD',14963,'BUILD',0 UNION ALL
SELECT 14964,'CODECHANGE',1002,'HW',2 UNION ALL
SELECT 14964,'CODECHANGE',1029,'3CC1',1 UNION ALL
SELECT 14964,'CODECHANGE',14964,'CODECHANGE',0 UNION ALL
SELECT 14965,'DBUPDATE',1002,'HW',2 UNION ALL
SELECT 14965,'DBUPDATE',1029,'3CC1',1 UNION ALL
SELECT 14965,'DBUPDATE',14965,'DBUPDATE',0 UNION ALL
SELECT 1034,'OTHER MAINT',1002,'HW',3 UNION ALL
SELECT 1034,'OTHER MAINT',1029,'3CC1',2 UNION ALL
SELECT 1034,'OTHER MAINT',1033,'MAINTENANCE',1 UNION ALL
SELECT 1034,'OTHER MAINT',1034,'OTHER',0 

--PARENT AND CHILD CLASSIFICATIONS--
SELECT *
FROM @CLASSSTRUCTURE


CSID        CLASSID              PARENT      DESCR

1002        HW                   NULL        Hardware
1029        3CC1                 1002        3CC1
1030        ACCESS               1029        Access
14963       BUILD                1029        Build
14964       CODECHANGE           1029        Code Change
14965       DBUPDATE             1029        Database Update
1032        INSTALL              1029        Install
1033        MAINTENANCE          1029        Maintenance
1034        OTHER                1033        Other Maint


---CLASSANCESTOR TABLE THAT ENUMERATES HIERARCHYLEVELS IN REVERSE--
SELECT	*,ROW_NUMBER() OVER (PARTITION BY CSID ORDER BY hierarchylevels DESC) AS CSLEVEL --I USED ROW NUMBER TO GET LEVELS
FROM	@CLASSANCESTOR

Note the level starts at 0 and if it has more than one level it will be in reverse order hence 2\1\0 is supposedly 0\1\2.


CSID	CLASSID 	ANCESTORCSID	ANCESORTCLASSID	HIERARCHYLEVELS	CSLEVEL
 
1001	SW		1001		SW		0		1
1002	HW		1002		HW		0		1
1029	3CC1		1002		HW		1		1
1029	3CC1		1029		3CC1		0		2
1030	ACCESS		1002		HW		2		1
1030	ACCESS		1029		3CC1		1		2
1030	ACCESS		1030		ACCESS		0		3
1032	INSTALL		1002		HW		2		1
1032	INSTALL		1029		3CC1		1		2
1032	INSTALL		1032		INSTALL		0		3
1033	MAINTENANCE	1002		HW		2		1
1033	MAINTENANCE	1029		3CC1		1		2
1033	MAINTENANCE	1033		MAINTENANCE	0		3
1034	OTHER MAINT	1002		HW		3		1
1034	OTHER MAINT	1029		3CC1		2		2
1034	OTHER MAINT	1033		MAINTENANCE	1		3
1034	OTHER MAINT	1034		OTHER		0		4
14963	BUILD		1002		HW		2		1
14963	BUILD		1029		3CC1		1		2
14963	BUILD		14963		BUILD		0		3
14964	CODECHANGE	1002		HW		2		1
14964	CODECHANGE	1029		3CC1		1		2
14964	CODECHANGE	14964		CODECHANGE	0		3
14965	DBUPDATE	1002		HW		2		1
14965	DBUPDATE	1029		3CC1		1		2
14965	DBUPDATE	14965		DBUPDATE	0		3

I tried to use the rownumber to figure out the levels, however doing so makes the my query extremely slow. This is the query that sort of works, but produces incorrect levels.

Here is my current query:


SELECT     
	classstructureid AS CSID
	,classificationid AS CLASSID
	,PARENT
	,description AS DESCR
	--CLASSIFICATION LEVELS
	,(
		SELECT
			ancestorclassid
		FROM 
			classancestor 
		WHERE
			(classstructureid = CS.classstructureid) 
			AND (hierarchylevels = 2)
	) AS LEVEL1
	,(
		SELECT
			ancestorclassid
		FROM 
			classancestor 
		WHERE
			(classstructureid = CS.classstructureid) 
			AND (hierarchylevels = 1)
	) AS LEVEL2	
	,(
		SELECT
			ancestorclassid
		FROM 
			classancestor 
		WHERE
			(classstructureid = CS.classstructureid) 
			AND (hierarchylevels = 0)
	) AS LEVEL3	
	--BUILD CLASSIFICATION HIERARCHY
	,CASE 
		WHEN --HAS 3 CLASSIFICATIONS
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 2)
			) IS NOT NULL 
		THEN
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 2)
			) + '\' + 
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 1)
			) + '\' +
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 0)
			) 
		WHEN --HAS 2 CLASSIFICATIONS
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 2)
			) IS NULL 
			AND 
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 1)
			) IS NOT NULL 
		THEN
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 1)
			) + '\' +
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 0)
			) 
		WHEN --HAS 1 CLASSIFICATION
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 1)
			) IS NULL 
			AND
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 0)
			) IS NOT NULL 
		THEN
			(
			SELECT	ancestorclassid
			FROM	classancestor 
			WHERE	(classstructureid = CS.classstructureid) AND (hierarchylevels = 0)) 
	END AS PATH
FROM
	classstructure AS CS
ORDER BY PATH


INCORRECT QUERY RESULT														
CSID	CLASSID 	PARENT	DESCR		LEVEL1	LEVEL2	LEVEL3		PATH		
		
1002	HW		NULL	Hardware	NULL	NULL	HW		HW		
1029	3CC1		1002	3CC1		NULL	HW	3CC1		HW\3CC1		
1030	ACCESS		1029	Access		HW	3CC1	ACCESS		HW\3CC1\ACCESS		
14963	BUILD		1029	Build		HW	3CC1	BUILD		HW\3CC1\BUILD		
14964	CODECHANGE	1029	Code Change	HW	3CC1	CODECHANGE	HW\3CC1\CODECHANGE		
14965	DBUPDATE	1029	Database Update	HW	3CC1	DBUPDATE	HW\3CC1\DBUPDATE		
1032	INSTALL		1029	Install		HW	3CC1	INSTALL		HW\3CC1\INSTALL		
1033	MAINTENANCE	1029	Maintenance	HW	3CC1	MAINTENANCE	HW\3CC1\MAINTENANCE		
1034	OTHER		1033	Other Maint	HW	3CC2	MAINTENANCE	HW\3CC1\MAINTENANCE\OTHER		


DESIRED QUERY RESULT										
CSID	CLASSID 	PARENT	DESCR		LEVEL1	LEVEL2	LEVEL3		LEVEL4	PATH

1002	HW		NULL	Hardware	HW	NULL	NULL		NULL	HW
1029	3CC1		1002	3CC1		HW	3CC1	NULL		NULL	HW\3CC1
1030	ACCESS		1029	Access		HW	3CC1	ACCESS		NULL	HW\3CC1\ACCESS
14963	BUILD		1029	Build		HW	3CC1	BUILD		NULL	HW\3CC1\BUILD
14964	CODECHANGE	1029	Code Change	HW	3CC1	CODECHANGE	NULL	HW\3CC1\CODECHANGE
14965	DBUPDATE	1029	Database Update	HW	3CC1	DBUPDATE	NULL	HW\3CC1\DBUPDATE
1032	INSTALL		1029	Install		HW	3CC1	INSTALL		NULL	HW\3CC1\INSTALL
1033	MAINTENANCE	1029	Maintenance	HW	3CC1	MAINTENANCE	NULL	HW\3CC1\MAINTENANCE
1034	OTHER		1033	Other Maint	HW	3CC2	MAINTENANCE	OTHER	HW\3CC1\MAINTENANCE\OTHER

Any input will be greatly appreciated. Thanks in advance.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-27 : 05:48:05

What is this @Classancestor-table for? Seems like none of it's data is used in the desired output. But your problem is far easier solved with a recursive cte query:

;WITH cte AS (
	SELECT 
		x.CSID, x.CLASSID, x.PARENT, x.DESCR, Level = 0, Path = CAST('\' + x.CLASSID as varchar(max))
	FROM @CLASSSTRUCTURE x
	WHERE x.PARENT IS NULL
	
	UNION ALL
	
	SELECT 
		a.CSID, a.CLASSID, a.PARENT, a.DESCR, Level = b.Level + 1, Path = b.Path + '\' + a.CLASSID
	FROM @CLASSSTRUCTURE a
		INNER JOIN cte b
			ON b.CSID = a.PARENT
	)
SELECT * FROM cte

It doesn't have the level-columns in it (yet) but it's just a matter of splitting the Path properly...

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-27 : 06:06:44

Here you go, a bit lame with the sub-selects but it does the trick:


WITH cte AS (
	SELECT 
		x.CSID, x.CLASSID, x.PARENT, x.DESCR, Level = 1, Path = CAST('\' + x.CLASSID as varchar(max))
	FROM @CLASSSTRUCTURE x
	WHERE x.PARENT IS NULL
	
	UNION ALL
	
	SELECT 
		a.CSID, a.CLASSID, a.PARENT, a.DESCR, Level = b.Level + 1, Path = b.Path + '\' + a.CLASSID
	FROM @CLASSSTRUCTURE a
		INNER JOIN cte b
			ON b.CSID = a.PARENT
	)
SELECT *,
	Level1 = (SELECT TOP 1 CLASSID FROM cte WHERE Level = 1 AND Level <= a.Level),
	Level2 = (SELECT TOP 1 CLASSID FROM cte WHERE Level = 2 AND Level <= a.Level),
	Level3 = (SELECT TOP 1 CLASSID FROM cte WHERE Level = 3 AND Level <= a.Level),
	Level4 = (SELECT TOP 1 CLASSID FROM cte WHERE Level = 4 AND Level <= a.Level)
FROM cte a

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-27 : 06:10:42

Arrrgh...crap...not right after all. Close but not there yet.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-27 : 07:24:42

Reeeally lame subselects, there are almost certainly better ways to split this Path-string, but I think it gives the right result now

;WITH cte AS (
	SELECT 
		x.CSID, x.CLASSID, x.PARENT, x.DESCR, Level = 1, Path = CAST('\' + x.CLASSID as varchar(max))
	FROM @CLASSSTRUCTURE x
	WHERE x.PARENT IS NULL
	
	UNION ALL
	
	SELECT 
		a.CSID, a.CLASSID, a.PARENT, a.DESCR, Level = b.Level + 1, Path = b.Path + '\' + a.CLASSID
	FROM @CLASSSTRUCTURE a
		INNER JOIN cte b
			ON b.CSID = a.PARENT
	)
SELECT *,
	Level1 = CASE WHEN Level = 1 THEN CLASSID ELSE (SELECT TOP 1 CLASSID FROM cte WHERE Level = 1 AND Level <= a.Level) END,
	Level2 = CASE WHEN Level = 2 THEN CLASSID ELSE (SELECT TOP 1 CLASSID FROM cte WHERE Level = 2 AND Level <= a.Level) END,
	Level3 = CASE WHEN Level = 3 THEN CLASSID ELSE (SELECT TOP 1 CLASSID FROM cte WHERE Level = 3 AND Level <= a.Level) END,
	Level4 = CASE WHEN Level = 4 THEN CLASSID ELSE (SELECT TOP 1 CLASSID FROM cte WHERE Level = 4 AND Level <= a.Level) END
FROM cte a

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

secuchalan
Starting Member

19 Posts

Posted - 2012-04-27 : 18:28:18

Lumbago thank you for your help, however when I ran this exact query against the classstructure table with 13514 rows it took more than 6 minutes to finish and in turn provided the correct path but gave incorrect level results. I think it's because it is referencing previous records?

This list will be joined to a ticket table for reference and that has 100,000+ records currently. I'm afraid joining the tables will make it slow.


classstructureid	classificationid	parent	description	Level	Path			Level1		Level2		Level3		Level4
1002			HW			NULL	Hardware	1	\HW			HW		NULL		NULL		NULL
1029			3CC1			1002	3CC1		2	\HW\3CC1		44000000	3CC1		NULL		NULL
1030			ACCESS			1029	Access		3	\HW\3CC1\ACCESS		44000000	81112305	ACCESS		NULL
14963			BUILD			1029	Build		3	\HW\3CC1\BUILD		44000000	81112305	BUILD		NULL
14964			CODECHANGE		1029	Code Change	3	\HW\3CC1\CODECHANGE	44000000	81112305	CODECHANGE
14965			DBUPDATE		1029	Database Update	3	\HW\3CC1\DBUPDATE	44000000	81112305	DBUPDATE
1032			INSTALL			1029	Install		3	\HW\3CC1\INSTALL	44000000	81112305	INSTALL		NULL
1033			MAINTENANCE		1029	Maintenance	3	\HW\3CC1\MAINTENANCE	44000000	81112305	MAINTENANCE	NULL
									
(8	row(s)	affected)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-04-27 : 22:51:28

This gives you your desired results, but but I am not sure how you want to use your class ansester table, but it should be easy to figure out from here.


drop function f_GetEntryDelimiitted
go
Create function f_GetEntryDelimiitted
	(
	@line varchar(4000) ,
	@fldnum int ,
	@delim varchar(10) ,
	@quoted varchar(1)	-- Y/N
	)
returns varchar(400)
as
begin
declare	@i int ,
	@j int ,
	@delimUsed varchar(11) ,
	@s varchar(4000)

	select @i = 1
	while	@fldnum > 0
	begin
		select @fldnum = @fldnum - 1
		if substring(@line, @i, 1) = '"' and @Quoted = 'Y'
		begin
			
			select 	@delimUsed = '"' + @Delim ,
				@i = @i + 1
		end
		else
		begin
			select @delimUsed = @Delim
		end
		
		select @j = charindex(@delimUsed, @line, @i)
		if @j = 0
			select @j = datalength(@line) + 1
		if @fldnum > 0
			select @i = @j +len(@delimused)
	end
	
	select @s = substring(@line, @i, @j - @i)
	return	@s
end

go



--Populate test data
DECLARE @CLASSSTRUCTURE
TABLE (CSID INT, CLASSID VARCHAR(20), PARENT INT, DESCR VARCHAR(200))
INSERT INTO @CLASSSTRUCTURE (CSID, CLASSID, PARENT, DESCR)
SELECT 1002,'HW',NULL,'Hardware' UNION ALL
SELECT 1029,'3CC1',1002,'3CC1' UNION ALL
SELECT 1030,'ACCESS',1029,'Access' UNION ALL
SELECT 14963,'BUILD',1029,'Build' UNION ALL
SELECT 14964,'CODECHANGE',1029,'Code Change' UNION ALL
SELECT 14965,'DBUPDATE',1029,'Database Update' UNION ALL
SELECT 1032,'INSTALL',1029,'Install' UNION ALL
SELECT 1033,'MAINTENANCE',1029,'Maintenance' UNION ALL
SELECT 1034,'OTHER',1033,'Other Maint' 

--Populate test data



;with mycte
as
(
select *, cast(classid as varchar(max)) as p,0 as itteration
from @CLASSSTRUCTURE
where parent is null
union all
select b.*, p  + '\' + b.CLASSID ,a.itteration + 1
from mycte a
inner join @CLASSSTRUCTURE  b
on a.CSID  = b.PARENT 
)
select csid,classid,parent,descr
,dbo.f_GetEntryDelimiitted(p,1,'\','N') as level1
,case when itteration >= 1 then dbo.f_GetEntryDelimiitted(p,2,'\','N') else null end as level2 
,case when itteration >= 2 then dbo.f_GetEntryDelimiitted(p,3,'\','N') else null end as level3 
,case when itteration >= 3 then dbo.f_GetEntryDelimiitted(p,4,'\','N') else null end as level4
,p as path
from
mycte ab
order by p

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-28 : 12:10:46

Can you try to run the query against the real table with just the "select * from cte" and see how long it takes? Figuring out the Level1-4 columns is just a matter of decomposing the Path and that can be done far more efficiently than what I did. And how is the index situation? Do the CSID and Parent columns have indexes?

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-28 : 12:14:52

You should also look at this link, sql server has some built-in fuctions to handle hierarchy data:
http://msdn.microsoft.com/en-us/library/bb677173(v=sql.105).aspx

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources