January 28, 2011 at 2:06 pm
I have a table that contains the origination structure e.g. - org, facility, department, unit. I need to join, not sure that is the right word, together the "Name" field to show the hierarchy for each unit. Ultimatlely each row will look like this:
(Using the group names here)
Organization::Facility::Department::Unit
Notice how the tables last column is called "parentid". This is a reference to the parents OrgNodeID. Each unit has a parent, each Department has a parent, each facility has a parent(all the same parent), and the organization has no parent.
using the unit BRK-4 East Cardicac Step down as and example I would end up with
SFH::AcutCare::UMCB::B-4E
If you look at it form a parentid perspective: (parentid being the OrgNodeID of it's parent)
NULL::1::106::107
If the data is in a table as below, what would my select statement look like? I have tried many variations to get to the my desired result but I have no success.
Thanks in advance
DECLARE @mytab TABLE
(
OrgNodeID int,
[Name] nvarchar(4000),
DisplayName nvarchar(4000),
GroupName nvarchar(4000),
ParentId nvarchar (5)
)
INSERT INTO @mytab
Select '1','SFH','My Big Hospital','Organization','NULL'
Union Select '106','AcutCare','Acute Care','Facility','1'
Union Select '107','UMCB','B-Acute Care','Department','106'
Union Select '108','B-4E','BRK-4 EAST Cardiac Stepdown ','Unit','107'
Union Select '109','B-7EW','BRK-7 EAST WEST','Unit','107'
Union Select '110','B-8EW','BRK-8 EAST WEST','Unit','107'
Union Select '111','B-9EW','BRK-9 EAST WEST','Unit','107'
Union Select '112','EBD','E-Acute Care','Department','106'
Union Select '113','E-MedSur','EBD-Med Surg','Unit','112'
Union Select '114','HMC','HM-Acute Care','Department','106'
Union Select '115','H-3rd','HMC-3rd Med Surg','Unit','114'
Union Select '116','H-4th','HMC-4th Med Surg','Unit','114'
Union Select '117','H-3h8h','zzHMC-3rd Med Surg 8hr','Unit','114'
Union Select '118','HL','H-Acute Care','Department','106'
Union Select '119','HL-MedSu','HL-Med Surg','Unit','118'
Union Select '120','MCA','M-Acute Care','Department','106'
Union Select '121','M-4N','MCA-4 North','Unit','120'
Union Select '122','M-5NS','MCA-5 North-South','Unit','120'
Union Select '123','M-6NS','MCA-6 North-South','Unit','120'
Union Select '124','M-7N','MCA-7 North','Unit','120'
Union Select '125','NW','N-Acute Care','Department','106'
Union Select '126','N-MedSur','NW-Med Surg','Unit','125'
Union Select '127','SW','S-Acute Care','Department','106'
Union Select '128','S-PCU','SW-Patient Care Unit','Unit','127'
Union Select '129','WMC','W-Acute Care','Department','106'
Union Select '130','W-3rd','WMC-3rd Med Surg','Unit','129'
Union Select '131','W-4th','WMC-4th Med Surg','Unit','129'
Select * from @mytab
January 28, 2011 at 2:57 pm
Thanks for the sample data. This should do the trick.
Note that this won't work until you fix the 'NULL' in your sample data to be just NULL instead.
;WITH C1 AS (
SELECT Name, OrgNodeID, ParentID, 1 Lvl, CAST(Name AS varchar(MAX)) AS NN
FROM @mytab
WHERE ParentID IS NULL
UNION ALL
SELECT M.Name, M.OrgNodeID, M.ParentID, C1.Lvl + 1, CAST(C1.NN + ' :: ' + M.Name AS varchar(MAX))
FROM c1
INNER JOIN @mytab M ON C1.OrgNodeID = M.ParentID)
SELECT * FROM C1
WHERE Lvl = 4
January 28, 2011 at 3:21 pm
Seth,
You are awesome. Work like a charm. Any chance of giving a breif explanation of what it is doing. I am not familiar with the ";with C1 AS" nor do I understand what the AST(C1.NN + ' :: ' + M.Name AS varchar(MAX) is doing.
Either way, THANKS!
January 31, 2011 at 6:28 am
Glad I could help. As to the explanations:
The ';WITH C1 AS' is the beginning declaration of a Common Table Expression (or CTE). In this case it's a recursive CTE. These are useful in certain circumstances for performing iterative logic, such as navigating the path of a hierarchy as you're doing here.
The cast is there to force the correct data type between the union, and the rest of it is basically tacking on each subsequent M.Name onto the previous string. If you run it without the 'WHERE Level = 4' that'll make a bit more sense. You'll see that "Level" is increasing for each tier that it navigates through the hierarchy and an additional name is tacked onto the string.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply