外键列的SQL透视

Question

我有一个这样的表:财政年度，地区，国家，Office1，Office2，Office3，Office4

其中office 1-4是外键。

我希望获得如下输出: Office 1:财年，地区，国家办事处2:财年，地区，国家办事处3:财年，地区，国家办事处4:财年，地区，国家

使用pivot可以做到这一点吗？

Answer 1

我觉得这更像是UNPIVOT：

SELECT [Fiscal Year], Region, County, OFfice
FROM 
   (SELECT [Fiscal Year], Region, County, OFfice1, Office2, Office3, Office4
   FROM unpvt) p
UNPIVOT
   (yourtable FOR Office IN 
      (Office1, Office2, Office3, Office4)
) AS unpvt;

但您也可以使用一个简单的查询来完成此操作：

select [Fiscal Year], Region, County, OFfice1 
from yourtable
union
select [Fiscal Year], Region, County, OFfice2 
from yourtable
union
select [Fiscal Year], Region, County, OFfice3
from yourtable
union
select [Fiscal Year], Region, County, OFfice4
from yourtable