我有两个表，如下所示
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
联接表并使用条件聚合:
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
请参阅演示。
结果:
| location_id | Parcel_ID | Zoning_Districts | CC_Districts |
| ----------- | --------- | ---------------- | ------------ |
| 6846 | L3300 | E - Big South | |
