嗨,我有两个表Attribute,Instance
属性表
id | key_info | value_info
2 | amount | 1009
2 | currency | USD
2 | Level | Level 5
3 | amount | 2017
3 | currency | CAD
实例表
id | status
2 | Pending
3 | Approved
我想加入两个这样的表-
新表
id | amount | currrency | level | status
2 | 1001 | USD | Level 5 | Pending
3 | 2017 | CAD | | Approved
“属性”和“实例”中的所有字段都是可选的,但id除外。
最佳答案
select
a.id,
max(case when a.key_info = 'amount' then a.value_info end) as amount,
max(case when a.key_info = 'currency' then a.value_info end) as currency,
max(case when a.key_info = 'level' then a.value_info end) as level,
i.status
from
attribute a
join instance i on a.id = i.id
group by
a.id,
i.status
Sql Fiddle
关于sql - 将行转置为列并联接表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/15234212/