May 15, 2014 at 8:20 pm
Hi, I would appreciate any help in displaying a list of parent and child objects in a certain way, to give you an idea of what I'm looking to do here's the code to create and populate an example dataset.
CREATE TABLE OrgStructure
(orgunitID int,
OrgUnitName varchar (100),
ParentID int)
INSERT INTO OrgStructure
VALUES
(1,'OrgUnit 1', 0), (2, 'OrgUnit 2', 0), (3, 'OrgUnit 3', 0),
(4, 'OrgUnit 1.1',1), (5, 'OrgUnit 2.1', 2), (6, 'OrgUnit 3.1', 3),
(7, 'OrgUnit 1.1.1',4),(8, 'OrgUnit 1.1.2',4),(9, 'OrgUnit 1.1.3',4),
(10,'OrgUnit 2.1.1',5),(11, 'OrgUnit 2.1.2',5),(12, 'OrgUnit 2.1.3',5),
(13, 'OrgUnit 3.1.1',6),(14, 'OrgUnit 3.1.2',6),(15, 'OrgUnit 3.1.3',6)
The way I'd like to display this data would be:
Parent OUChild OU1Child OU2
OrgUnit 1 OrgUnit 1.1OrgUnit 1.1.1
NULLNULLOrgUnit 1.1.2
NULLNULLOrgUnit 1.1.3
OrgUnit 2 OrgUnit 2.1OrgUnit 2.1.1
NULLNULLOrgUnit 2.1.2
NULLNULLOrgUnit 2.1.3
OrgUnit 3 OrgUnit 3.1OrgUnit 3.1.1
NULLNULLOrgUnit 3.1.2
NULLNULLOrgUnit 3.1.3
The 3 OrgUnits with no parentID in one column (parentOU), then any OrgUnits who's parentID matches the orgunitid of the 3 OrgUnits in ParentOU in the next column (Child OU1), followed by any OrgUnits who's parentID matched the orgunitID of the OrgUnits in 'Child OU1' would be in the third column (Child OU2)
The dataset I'm working on contains ~700 rows and the OrgUnitNames can be anything, they aren't labelled like the example dataset with 1.1.1 and so on.
Let me know if I need to flesh this out a bit more, appreciate any help as I cant seem to get over the line with this one.
May 15, 2014 at 10:07 pm
This is a simple solution that uses the row_number function with a case statement to hide/null part of the output.
😎
USE tempdb
go
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'OrgStructure'
AND TABLE_SCHEMA = N'dbo')
DROP TABLE dbo.OrgStructure;
CREATE TABLE dbo.OrgStructure
(orgunitID int,
OrgUnitName varchar (100),
ParentID int);
INSERT INTO dbo.OrgStructure
VALUES
(1,'OrgUnit 1', 0), (2, 'OrgUnit 2', 0), (3, 'OrgUnit 3', 0),
(4, 'OrgUnit 1.1',1), (5, 'OrgUnit 2.1', 2), (6, 'OrgUnit 3.1', 3),
(7, 'OrgUnit 1.1.1',4),(8, 'OrgUnit 1.1.2',4),(9, 'OrgUnit 1.1.3',4),
(10,'OrgUnit 2.1.1',5),(11, 'OrgUnit 2.1.2',5),(12, 'OrgUnit 2.1.3',5),
(13, 'OrgUnit 3.1.1',6),(14, 'OrgUnit 3.1.2',6),(15, 'OrgUnit 3.1.3',6);
SELECT
CASE
WHEN ROW_NUMBER() OVER
(
PARTITION BY OP.orgunitID
ORDER BY OP.orgunitID
) = 1 THEN OP.OrgUnitName
ELSE NULL
END AS [Parent OU]
,CASE
WHEN ROW_NUMBER() OVER
(
PARTITION BY OM.orgunitID
ORDER BY OM.orgunitID
) = 1 THEN OM.OrgUnitName
ELSE NULL
END AS [Child OU1]
,OC.OrgUnitName AS [Child OU2]
FROM dbo.OrgStructure OP
INNER JOIN dbo.OrgStructure OM
ON OP.orgunitID = OM.ParentID
INNER JOIN dbo.OrgStructure OC
ON OM.orgunitID = OC.ParentID
WHERE OP.ParentID = 0
Results
Parent OU Child OU1 Child OU2
---------- ------------ --------------
OrgUnit 1 OrgUnit 1.1 OrgUnit 1.1.1
NULL NULL OrgUnit 1.1.2
NULL NULL OrgUnit 1.1.3
OrgUnit 2 OrgUnit 2.1 OrgUnit 2.1.1
NULL NULL OrgUnit 2.1.2
NULL NULL OrgUnit 2.1.3
OrgUnit 3 OrgUnit 3.1 OrgUnit 3.1.1
NULL NULL OrgUnit 3.1.2
NULL NULL OrgUnit 3.1.3
May 15, 2014 at 10:34 pm
Thanks very much for taking the time to look at this Eirikur, this works well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply