mysql - 在 mysql V2 中将行转换为列

Question

引用这篇文章:https://stackoverflow.com/questions/19985340/convert-rows-to-columns-in-mysql

我有这段代码，它连接了两个具有差异列名称的表，我想我可以这样做，以便我可以使用bluefeet的代码，因为我有单独的表，其中字符串位置位于table_1上，位置id为在表_2 上。看起来像这样

表_1:

id | location
1  | East Flow
2  | East Level
3  | East Pressure
4  | East MR
5  | West Flow
6  | West Level
7  | West Pressure
8  | West MR

表2:

locationid | val
   1       | 10
   2       | 20
   3       | 30
   4       | 40
   5       | 100
   6       | 200
   7       | 300
   8       | 400

因此，当您执行此查询时，它将如下所示:

SELECT id, locationid, location, val 
FROM table_1, table_2
WHERE id = locationid 
GROUP BY id

输出:

id   | locationid |     location    | val
1    |     1      |   East Flow     | 10
2    |     2      |   East Level    | 20
3    |     3      |   East Pressure | 30
4    |     4      |   East MR       | 40
5    |     5      |   West Flow     | 100
6    |     6      |   West Level    | 200
7    |     7      |   West Pressure | 300
8    |     8      |   West MR       | 400

<小时/>

我想将 @bluefeet 的代码合并到我的代码中，以便我可以使用她的代码，因为她的代码已经可以工作了:

select 
  substring_index(location, ' ', 1) Location,
  max(case when location like '%Flow' then val end) Flow,
  max(case when location like '%Level' then val end) Level,
  max(case when location like '%Pressure' then val end) Pressure,
  max(case when location like '%MR' then val end) MR
from yourtable
group by substring_index(location, ' ', 1)

我如何合并它？在选择中选择还是什么？这就是我希望输出的样子:

从此:

  Location    | Val |
East Flow     | 10  |
East Level    | 20  |
East Pressure | 30  |
East MR       | 40  |
West Flow     | 100 |
West Level    | 200 |
West Pressure | 300 |
West MR       | 400 |

对此:

Location | Flow| Level | Pressure |  MR   |
East     | 10  |  20   |    300   |  400  |
West     | 100 |  200  |    300   |  400  |

Answer 1

您应该能够只需加入表即可获得结果:

select 
  substring_index(t1.location, ' ', 1) Location,
  max(case when t1.location like '%Flow' then t2.val end) Flow,
  max(case when t1.location like '%Level' then t2.val end) Level,
  max(case when t1.location like '%Pressure' then t2.val end) Pressure,
  max(case when t1.location like '%MR' then t2.val end) MR
from table_1 t1
inner join table_2 t2
  on t1.id = t2.locationid
group by substring_index(t1.location, ' ', 1)

参见SQL Fiddle with Demo