SQL Server - 查询以根据 columnName 字段连接两个表

Question

我有一个现有的 SQL Server 表结构，如下所示:

--Entries Table--
*Company* | *MusicProduct* | *FoodProduct* | *PCProduct* | *PhoneProduct* |
  Apple         iPod             null           iMac           iPhone
  Dell          null             null          Dellbook         null

上表的主键是 Company

--Questions Table--
*ColumnName*    |   Questions
 MusicProduct        What mp3 device the company known for?
 FoodProduct         What food product describes the company best?
 PCProduct           What PC?
 PhoneProduct        What phone does the company give employees?

上表的主键是 ColumnName它包含第一个表中每个非键列的行。这当然感觉像是糟糕的设计，但它预先存在于我的工作中。

对于选定的单个公司，我基本上想要一个查询，该查询将返回带有问题和答案的行，忽略带有空答案的问题。 Apple 的输出如下所示(三行):

  Question                                        Answer
  What mp3 device the company known for?          iPod
  What PC?                                        iMac
  What phone does the company give employees?     iPhone

我怎样才能最好地完成上述工作？某种新的引用表会是要走的路吗？

Answer 1

请试试这个。

select q.question, product as answer
from
(select company, Musicproduct, Foodproduct, PCProduct, phoneproduct from Entries ) p
unpivot ( product for Products in (Musicproduct, Foodproduct, PCProduct, phoneproduct)
) unpvt
join questions q on unpvt.product = q.answer;