列的T-SQL条件移位

Question

我目前有两个SQL查询的联合，结果是我的业务的组织结构图完整。查询如下：

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

..。而且效果很好。除担任监督职务的人员外。下面是输出的样子(在本例中，Servo报告给Robinson，谁向Forrester报告)：

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

有没有办法强迫Joel‘Deep 13和National像Servo(即空/深-13/National)一样排列，Forrester也像Servo(即NULL/NULL/National)一样排队？

Answer 1

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

Answer 2

首先，在[Boss].[dbo].[Organizations] Org1上设置一个别名，因为您将需要它。

然后将最后3项选择更改为：

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