CODEWITHSUNDEEP
sql-servert-sql
Don Ford
Don Ford

Reputation: 115

T-SQL Conditional Shifting of Columns

I currently have a union of two SQL queries that results in the entirety of the organizational chart for my business. The query is as follows:

SELECT [Boss].[dbo].[Persons].[PersonId]
  ,[FirstName]
  ,[MiddleName]
  ,[LastName]
  ,[Prefix]
  ,[Suffix]
  ,[Title]
  ,[CreateDate]
  ,[Boss].[dbo].[OrganizationPersons].[OrganizationId]
  ,[Boss].[dbo].[Organizations].[Name] AS [Region]
  ,Org2.Name AS [Division]
  ,Org3.Name AS [National]
FROM [Boss].[dbo].[Persons]
INNER JOIN [Boss].[dbo].[OrganizationPersons]
ON [Boss].[dbo].[Persons].[PersonId]=[Boss].[dbo].[OrganizationPersons].[PersonId]
INNER JOIN [Boss].[dbo].[Organizations]
ON [Boss].[dbo].[OrganizationPersons].[OrganizationId]=[Boss].[dbo].[Organizations].[OrganizationId]
INNER JOIN [Boss].[dbo].[OrganizationVersions]
ON [Boss].[dbo].[Organizations].[OrganizationVersionId]=[Boss].[dbo].[OrganizationVersions].[OrganizationVersionId]
LEFT OUTER JOIN [Boss].[dbo].[Organizations] Org2
ON Org2.OrganizationId=[Boss].[dbo].[Organizations].[ParentOrganizationId]
LEFT OUTER JOIN [Boss].[dbo].[Organizations] Org3
ON Org3.OrganizationId=Org2.ParentOrganizationId
WHERE [Boss].[dbo].[OrganizationVersions].[EndEffectiveDate] is NULL
UNION
SELECT [Boss].[dbo].[Persons].[PersonId]
  ,[FirstName]
  ,[MiddleName]
  ,[LastName]
  ,[Prefix]
  ,[Suffix]
  ,[Title]
  ,[CreateDate]
  ,[Boss].[dbo].[OrganizationManagers].[OrganizationId]
  ,[Boss].[dbo].[Organizations].[Name] AS [Region]
  ,Org2.Name AS [Division]
  ,Org3.Name AS [National]
FROM [Boss].[dbo].[Persons]
INNER JOIN [Boss].[dbo].[OrganizationManagers]
ON [Boss].[dbo].[Persons].[PersonId]=[Boss].[dbo].[OrganizationManagers].[PersonId]
INNER JOIN [Boss].[dbo].[Organizations]
ON [Boss].[dbo].[OrganizationManagers].[OrganizationId]=[Boss].[dbo].[Organizations].[OrganizationId]
INNER JOIN [Boss].[dbo].[OrganizationVersions]
ON [Boss].[dbo].[Organizations].[OrganizationVersionId]=[Boss].[dbo].[OrganizationVersions].[OrganizationVersionId]
LEFT OUTER JOIN [Boss].[dbo].[Organizations] Org2
ON Org2.OrganizationId=[Boss].[dbo].[Organizations].[ParentOrganizationId]
LEFT OUTER JOIN [Boss].[dbo].[Organizations] Org3
ON Org3.OrganizationId=Org2.ParentOrganizationId
WHERE [Boss].[dbo].[OrganizationVersions].[EndEffectiveDate] is NULL

... and it works great. EXCEPT for the people who are in supervisory positions. Here's what output looks like (Servo reports to Robinson, who reports to Forrester, in this example):

PersonID  FirstName  LastName  ... Region    Division  National
1         Tom        Servo     ... SOL       Deep-13   National
2         Joel       Robinson  ... Deep-13   National  NULL
3         Clayton    Forrester ... National  NULL      NULL

Is there a way to force Joel's Deep-13 and National to line up like Servo's (i.e., NULL/Deep-13/National) and Forrester's to line up like Servo's too (i.e., NULL/NULL/National)?

Upvotes: 0

Views: 43

Answers (2)

Mike K
Mike K

Reputation: 486

First, put an alias on [Boss].[dbo].[Organizations] Org1, because you're going to be needing that.

Then change your last 3 selections to:

CASE WHEN Org2.Name IS NULL THEN NULL ELSE Org1.Name END AS Region
CASE WHEN Org2.Name IS NULL THEN NULL
     WHEN Org3.Name IS NULL THEN Org1.Name
     ELSE Org2.Name
END AS Division,
COALESCE (Org3.Name, Org2.Name, Org1.Name) AS National

Upvotes: 1

shawnt00
shawnt00

Reputation: 17915

case
    when Org2.Name is not null and Org3.Name is not null then Org1.Name
    else null
end as Region,
case
    when Org3.Name is not null then Org2.Name
    when Org2.Name is not null then Organizations.Name
    else null
end as Division,
coalesce(Org3.Name, Org2.Name, Organizations.Name) as National

Upvotes: 0

Related Questions