| Author |
 Topic  |
|
|
secuchalan
Starting Member
19 Posts |
 Posted - 04/27/2012 : 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. |
Edited by - secuchalan on 04/27/2012 05:02:09
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 04/27/2012 : 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
Norway
3241 Posts |
Posted - 04/27/2012 : 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/ |
Edited by - Lumbago on 04/27/2012 06:08:48 |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 04/27/2012 : 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 - 04/27/2012 : 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)
|
Edited by - secuchalan on 04/27/2012 18:29:40 |
|
|
|
Vinnie881
Flowing Fount of Yak Knowledge
USA
1202 Posts |
Posted - 04/27/2012 : 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 |
Edited by - Vinnie881 on 04/28/2012 23:50:16 |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 04/28/2012 : 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
Norway
3241 Posts |
|
| |
Topic |
|
|
|
Hierarchy Path with Levels That Is Reversed
