嗨,我有两个表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/

10-16 15:05
查看更多