Splitting a Hierarchy Tree Field

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 2005 Forums

Transact-SQL (2005)

Splitting a Hierarchy Tree Field

Author

Topic

secuchalan
Starting Member

19 Posts

Posted - 2011-09-30 : 21:53:22

Given this hierarchy: SW\MICROSOFT\WORD\TROUBLESHOOT

What is the best way to split this hierarchy so I get the following result:
Level 1: SW
Level 2: MICROSOFT
Level 3: WORD
Level 4: TROUBLESHOOT

Where LEVEL 1 is the string before the first occurrence of '\', LEVEL 2 is the string between the first and 2nd occurrence of '\', LEVEL 3 is the string between 2nd and 3rd occurrence of '\' and
LEVEL 4 is the string after the 3rd occurrence of '\'.

Query should also be able to handle classifications that may not have all 4 levels.

Hence if the hierarchy is SW\MICROSOFT\WORD, the result should be:
Level 1: SW
Level 2: MICROSOFT
Level 3: WORD

Here is the query I started for this:


DECLARE @TEMP TABLE 
(
	ROWID INT IDENTITY(1, 1) PRIMARY KEY,
	[FULLHIERCLASS] [Nvarchar](1000) NULL
)

INSERT INTO @TEMP (FULLHIERCLASS)
        VALUES ('SW\MICROSOFT\WORD');
INSERT INTO @TEMP (FULLHIERCLASS)
        VALUES ('SW\MICROSOFT\WORD\TROUBLESHOOT');
INSERT INTO @TEMP (FULLHIERCLASS)
        VALUES ('SW\MICROSOFT');
INSERT INTO @TEMP (FULLHIERCLASS)
        VALUES ('SW');

SELECT FULLHIERCLASS
,LEFT(FULLHIERCLASS, NULLIF(CHARINDEX('\', FULLHIERCLASS) - 1, -1)) AS LEVEL1
,SUBSTRING(FULLHIERCLASS,(CHARINDEX('\', FULLHIERCLASS) + 1), LEN(FULLHIERCLASS)) AS LEVEL2
,SUBSTRING(FULLHIERCLASS,(CHARINDEX('\', FULLHIERCLASS,1+CHARINDEX('\', FULLHIERCLASS,1))+1),LEN(FULLHIERCLASS))AS LEVEL3
,SUBSTRING(FULLHIERCLASS,(CHARINDEX('\', FULLHIERCLASS,1+CHARINDEX('\', FULLHIERCLASS,1+CHARINDEX('\', FULLHIERCLASS,1)))+1),LEN(FULLHIERCLASS))AS LEVEL4
FROM @TEMP

And here is the INCORRECT result of my query :(



FULLHIERCLASS 				LEVEL1 	LEVEL2 				LEVEL3			LEVEL4
-------------------------------------------------------------------------------------------------------------------------
SW\MICROSOFT\WORD 			SW 	MICROSOFT\WORD			WORD			SW\MICROSOFT\WORD
SW\MICROSOFT\WORD\TROUBLESHOOT     	SW    	MICROSOFT\WORD\TROUBLESHOOT 	WORD\TROUBLESHOOT	TROUBLESHOOT
SW\MICROSOFT   				SW    	MICROSOFT 			SW\MICROSOFT		MICROSOFT
SW    					NULL   	SW    							SW

(4 row(s) affected)

Desired result:


FULLHIERCLASS 				LEVEL1 	LEVEL2 				LEVEL3			LEVEL4
-------------------------------------------------------------------------------------------------------------------------
SW\MICROSOFT\WORD 			SW 	MICROSOFT			WORD			NULL
SW\MICROSOFT\WORD\TROUBLESHOOT     	SW    	MICROSOFT		 	WORD 			TROUBLESHOOT
SW\MICROSOFT   				SW    	MICROSOFT 			NULL			NULL
SW    					SW 	NULL				NULL			NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-01 : 13:45:48

[code]SELECT FULLHIERCLASS
,COALESCE(PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),4),
PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),3),
PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),2),
PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),1)) AS LEVEL1
,
CASE
WHEN PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),2) IS NULL
THEN NULL
WHEN PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),3) IS NULL
THEN PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),1)
WHEN PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),4) IS NULL
THEN PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),2)
ELSE PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),3)
END
AS LEVEL2
,CASE
WHEN PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),3) IS NULL
THEN NULL
WHEN PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),4) IS NULL
THEN PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),1)
ELSE PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),2)
END
AS LEVEL3
,
CASE
WHEN PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),4) IS NULL
THEN NULL
ELSE PARSENAME(REPLACE([FULLHIERCLASS],'\','.'),1)
END
AS LEVEL4
FROM @TEMP[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

secuchalan
Starting Member

19 Posts

Posted - 2011-10-03 : 19:08:11

Thanks Visakh, this worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 00:45:37

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources