sql - 以行而不是单行显示 SQL 结果

Question

我有以下表格：

餐桌办公室：

OfficeID    MainAddId   SubAddId1   SubAddId2   PortAddId1
-------     --------    --------    --------    --------
  2             1           2           3           5

表地址：

AddressID   Street          City            ZipCode     State
-------     --------        --------        --------    --------
  1         Forest Ave      New York        10001       New York
  2         Morris St       Philadelphia    19019       Pennsylvania
  3         David St        Raleigh         27513       North Carolina

表端口：

PortID      PortName            Street          City            ZipCode     State
-------     -------             --------        --------        --------    --------
  5         New York Harbour    Bay St          New York        10001       New York

我想编写一个 SQL 请求，如果 office 表中的任何地址 ID 不为空，它将返回列表中的地址列表，如：

AddressID   Street          City            ZipCode     State
-------     --------        --------        --------    --------
1           Forest Ave      New York        10001       New York
2           Morris St       Philadelphia    19019       Pennsylvania
3           David St        Raleigh         27513       North Carolina
5           Bay St          New York        10001       New York

请帮助我如何做到这一点？谢谢

这是我尝试过的（部分原因是它不起作用）：

select *
from Office offi
left join Address add1 on offi.MainAddId = add1.AddressID
left join Address add2 on offi.SubAddId1= add2.AddressID
where offi.OfficeID = 2;

但是，这是在一行上返回地址。

标签： sqlsql-server

Answer 1

试试这个查询：

SELECT  *
  FROM  (   SELECT  AddressID, Street, City, ZipCode, State
              FROM  address
            UNION
            SELECT  PortID, Street, City, ZipCode, State
              FROM  Port) w
 WHERE  addressid IN (   SELECT Addresses
                           FROM (   SELECT  OfficeID, MainAddId, SubAddId1, SubAddId2, PortAddId1
                                      FROM  Offices) p
                             UNPIVOT (   Addresses
                                               FOR Offices IN (MainAddId, SubAddId1, SubAddId2, PortAddId1)) AS unpvt );