根据列值从另一个表调用值

Question

我有两个表，如下所示

table 1
-------   
type_id   type_title
=======   ===========
      1   Zoning District  
      2   Parcel_ID  
      3   CC Districts


table 2
-------   
location _id  type_id   store_value
============  =======   ===========
        6846        1   E - Big South  
        6846        2   L3300
        6846        3   

我想要显示以下输出

location_id  Parcel_ID  Zoning_Districts  CC_Districts
===========  =========  ================  ============
       6846  L3300      E - Big South

Answer 1

连接表并使用条件聚合：

select t2.location_id,
  max(case when t1.type_title = 'Parcel_ID' then t2.store_value end) Parcel_ID,  
  max(case when t1.type_title = 'Zoning District' then t2.store_value end) Zoning_Districts,
  max(case when t1.type_title = 'CC Districts' then t2.store_value end) CC_Districts  
from table2 t2 left join table1 t1
on t1.type_id = t2.type_id
group by t2.location_id

请参阅demo。

结果：

| location_id | Parcel_ID | Zoning_Districts | CC_Districts |
| ----------- | --------- | ---------------- | ------------ |
| 6846        | L3300     | E - Big South    |              |

Answer 2

;with location_cte
AS(
SELECT DISTINCT locationId
FROM table2
)
SELECT locationId,
(Select store_value FROM table2 t Where t.locationId = c.locationId And typeId =2),
...
...
...
FROM location_cte c

您需要从表2中获取所有不同的位置，然后对每一列执行一个查询。