SQL Pivot for foreign key column

Question

I have a table like so: Fiscal Year, Region, Country, Office1, Office2, Office3, Office4

Where office 1-4 are foreign keys.

I would like to get output like so: Office 1: Fiscal Year, Region, Country Office 2: Fiscal Year, Region, Country Office 3: Fiscal Year, Region, Country Office 4: Fiscal Year, Region, Country

Can this be done using pivot?

Answer 1

That's more like UNPIVOT I think:

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;

But you can do that with a simple query as well:

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