基于TypeID创建列

Question

这可能会让人困惑，但我很确定你们中的一个人可以帮我解决这个问题。所以基本上，我有一个包含以下内容的主表：

ObjID, InfoID, InfoVal, (other columns not needed).

When InfoID = 1, the InfoVal for that row represents the Report name. 
When InfoID = 4, the InfoVal for that row represents the Stored Procedures Name.
When InfoID = 16,17,23 or 24, the InfoVal for that row represents the Email for that Report.

我的问题是，如何准确地将每个条件转换为列并将其连接在一起？到目前为止，我有多个单独的查询，其中InfoID = ?？我想把每一列连接在一起。

本质上，我想要一个用于报告名称、存储过程名称的列和一个用于电子邮件的列。

如果你需要图片可视化，请让我知道！

谢谢。

Answer 1

您似乎想要条件聚合：

select objid,
       max(case when infoid = 1 then infoval end) as report_name,
       max(case when infoid = 4 then infoval end) as stored_procedure_name,
       max(case when infoid = 16 then infoval end) as email1,
       max(case when infoid = 17 then infoval end) as email2,
       max(case when infoid = 23 then infoval end) as email3,
       max(case when infoid = 24 then infoval end) as email4
from t
group by objid;

要过滤：

select t.*
from (select objid,
             max(case when infoid = 1 then infoval end) as report_name,
             max(case when infoid = 4 then infoval end) as stored_procedure_name,
             max(case when infoid = 16 then infoval end) as email1,
             max(case when infoid = 17 then infoval end) as email2,
             max(case when infoid = 23 then infoval end) as email3,
             max(case when infoid = 24 then infoval end) as email4
      from t
      group by objid
    ) t
where email1 like . . .