February 6, 2016 at 7:42 am
Hi All,
I know there are hundreds of articles , post etc.. on Pivoting but I have spent hours and either I am having a massive brain fart or my scenario just doesn't fit into the normal, PIVOT without Aggregate scenario. I have included code below to create test tables with example of what I am looking for. I am hoping someone can point me in the right direction.
I have two tables that contain an organisation's structure,i.e. Departments, Teams and Role.
The OrganisationParts table contains a PartType (Department,Team,Role) and the PartName (Dept 1, Dept 2, Team 10, Teams 20, Role 100 etc..)
The OrganisationStructure table contains a relationships between that parts, i.e. Team 10 is a member of Dept 1 and Role 100 is a member if Team 10 etc..)
The output I am looking for is:
DepartmentTeamRole
----------------------------
Depart 1Team 10Role 100
Depart 1Team 11Role 101
Depart 2Team 20Role 200
Depart 2Team 20Role 201
Depart 3Team 30Role 300
Depart 4Role 400
Depart 5Role 500
Here is the code to create for test data:
-- List of Departments, Teams and Role
CREATE TABLE [dbo].[OrganisationParts] (
[pkOrganisationParts] [INT] NOT NULL
,[PartType] [VARCHAR](50) NOT NULL
,[PartName] [VARCHAR](50) NOT NULL
)
INSERT INTO OrganisationParts (pkOrganisationParts, PartType, PartName)
VALUES (1, 'Department', 'Depart 1')
,(2, 'Department', 'Depart 2')
,(3, 'Department', 'Depart 3')
,(4, 'Department', 'Depart 4')
,(5, 'Department', 'Depart 5')
,(10, 'Team', 'Team 10')
,(11, 'Team', 'Team 11')
,(20, 'Team', 'Team 20')
,(30, 'Team', 'Team 30')
,(100, 'Role', 'Role 100')
,(101, 'Role', 'Role 101')
,(200, 'Role', 'Role 200')
,(201, 'Role', 'Role 201')
,(300, 'Role', 'Role 300')
,(400, 'Role', 'Role 400')
,(500, 'Role', 'Role 500');
SELECT * FROM OrganisationParts op
-- Relates Team to Department, Role to Department, Role to Team
CREATE TABLE [dbo].[OrganisationStructure] (
[pkOrganisationStructure] [INT] NOT NULL
,[fkParentPart] [INT] NOT NULL
,[fkChildPart] [INT] NOT NULL
)
INSERT INTO OrganisationStructure (pkOrganisationStructure, fkParentPart, fkChildPart)
VALUES (1, 1, 10)-- Dpt 1 to Team 10
,(2, 1, 11) -- Dpt 1 to Team 11
,(3, 2, 20) -- Dpt 2 to Team 20
,(4, 3, 30) -- Dpt 3 to Team 30
,(5, 4, 400) -- Dpt 4 to Role 400
,(6, 5, 500) -- Dpt 5 to Role 500
,(7, 10, 100) -- Team 10 to Role 100
,(8, 10, 101) -- Team 10 to Role 101
,(9, 20, 200) -- Team 10 to Role 200
,(10, 20, 201) -- Team 10 to Role 201
,(11, 30, 300) -- Team 10 to Role 300
SELECT * FROM OrganisationStructure os
SELECT opp.PartType as ParentPartType
,opp.PartName as ParentPartName
,opc.PartType as ChildPartType
,opc.PartName as ChildPartName
FROM OrganisationStructure os
JOIN OrganisationParts opp ON os.fkParentPart = opp.pkOrganisationParts
JOIN OrganisationParts opc ON os.fkChildPart = opc.pkOrganisationParts
Any help at all is much appreciated
February 7, 2016 at 2:24 am
Personally I think what you are looking at is an hierarchy, try searching on that it may help. Although I am sure that someone with more time may come up with a solution in line with what you are asking.
...
February 7, 2016 at 3:37 am
First of all, if you have *any* control over the design of that database at all. Change it. Now. This design is a disaster waiting to happen. It is not only hard to query, it is also impossible to protect against data entry. With this design, how will you prevent someone erroneously misentering data that results in a team or role being the parent of a department. Or accidentally assiging a team to multiple departments.
There are some cases where a more flexible design has its advantages. This is not one of them. This is a very standard department / team / project setup (where project has been replaced by role - minor difference). You want a standard design, with one table for Departments, a second table for Teams (with a foreign key into Departments), and a third table for Roles. Based on your sample data I assume that a role is assigned to either a department or a team but not both; you can implement this using two foreign keys that are mutually exclusive. In T-SQL terms, have two columns to reference the two other tables, make them nullable, add a CHECK constraint to prevent both being NULL or both being NOT NULL, and add the two foreign key constraints (NULL values are not considered a violation of a foreign key). With those tables, your query, and most of the rest of your professional life, will probably be a lot easier.
If you really have to make do with the cr*ppy design you have posted, then here is at least a way to get the results you posted from that mess. I took the final query you posted as a starting point; perhaps it can be optimized further when I don't but I really could not be bothered to optimize this because I hope will never be actually used anyway.
WITH BaseInput
AS (SELECT opp.PartType as ParentPartType,
opp.PartName as ParentPartName,
opc.PartType as ChildPartType,
opc.PartName as ChildPartName
FROM dbo.OrganisationStructure AS os
INNER JOIN dbo.OrganisationParts AS opp
ON os.fkParentPart = opp.pkOrganisationParts
INNER JOIN dbo.OrganisationParts AS opc
ON os.fkChildPart = opc.pkOrganisationParts)
SELECT COALESCE(t.ParentPartName, r.ParentPartName) AS Department,
COALESCE(t.ChildPartName, '') AS Team,
r.ChildPartName AS [Role]
FROM BaseInput AS r
LEFT JOIN BaseInput AS t
ON r.ParentPartType = 'Team'
AND t.ChildPartType = r.ParentPartType
AND t.ChildPartName = r.ParentPartName
WHERE r.ChildPartType = 'Role'
ORDER BY r.ChildPartName;
February 7, 2016 at 4:01 am
Hello Hugo,
Thanks very much for your input, I will see what I can do regarding the design.
Also thanks for your code example, don't know why I was thinking of pivot, as mentioned a day full of brain farts 🙂
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply