如何将sql结果条目旋转到列中(数据透视) [英] How rotate sql result entries into columns (pivot)

问题描述

我有一张桌子

| id | value   | comment   |
|--------------------------|
|  1 | Some1   | comm1     |
|--------------------------|
|  2 | Some2   | comm2     |
|--------------------------|

我有表b，其中表a为外键

and i have table b with table a as foreign key

| id | id_a  |name    | amount    | factor   |
|--------------------------------------------|
|  1 |  1    |Car     | 12        | 2        |
|--------------------------------------------|
|  2 |  1    |Bike    | 22        | 5        |
|--------------------------------------------|
|  3 |  2    |Car     | 54        | 1        |
|--------------------------------------------|
|  4 |  2    |Bike    | 55        | 4        |
|--------------------------------------------|

结果，我想组合:

|id| value | comment | Car_Amount | Car_factor | Bike_Amount | Bike_Factor |
|--------------------------------------------------------------------------|
| 1| Some1 | comm1   | 12         | 2          | 22          | 5           |
|--------------------------------------------------------------------------|
| 2| Some2 | comm2   | 54         | 1          | 55          | 4           |
|--------------------------------------------------------------------------|

据我所知，这不是关键.但是我不确定这是否是好的做法.我不是SQL方面的专家，但是混合这样的表看起来是完全错误的. 我的意思是他们"希望将其作为简单的结果用于报告...

It is not a pivot as far as I can see. But I am not sure if this is good practise at all. I am not an expert in SQL things, but it looks utterly wrong to mix tables like that. I mean "they" want to have it as a flat result to use it for reporting...

有可能吗?

谢谢

推荐答案

聚合这样的值:

select 
      a.id, a.value, a.comment,
      sum(case when b.name='Car'  then b.amount end) as Car_Amount,
      sum(case when b.name='Car'  then b.factor end) as Car_Factor,
      sum(case when b.name='Bike' then b.amount end) as Bike_Amount,
      sum(case when b.name='Bike' then b.factor end) as Bike_Factor
from a left join b on a.id=b.id_a
group by  a.id, a.value, a.comment;

这篇关于如何将sql结果条目旋转到列中(数据透视)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！