在MySQL中创建视图并将行转换为列 [英] Create View and Convert Rows into Column in MySQL

问题描述

我有一张桌子；表A.

+-----+----------+
|month|value_deal|
+-----+----------+
|JAN  |10        |
+-----+----------+
|JAN  |20        |
+-----+----------+
|FEB  |30        |
+-----+----------+
|FEB  |40        |
+-----+----------+

我想将value_deal行转换为具有相同月份的列，并使其成为视图，像这样.

I want to convert the rows value_deal as columns with the same month and make it as view, like this.

+-----+-----------+-----------+
|month|value_deal1|value_deal2|
+-----+-----------+-----------+
|JAN  |10         |20         |
+-----+-----------+-----------+
|FEB  |30         |40         |
+-----+-----------+-----------+

我尝试在此处.但是我无法将代码插入创建视图"函数中.

I've tried using dynamically convert rows to column in here. But I can't insert the code inside Create View functions.

任何人都可以帮忙吗?请

Can anyone help? pls

推荐答案

我为表test命名，以下查询返回所需结果

I named the table test and the following query returns the desired result

SELECT QRY.M,
       SUBSTRING_INDEX(SUBSTRING_INDEX(QRY.CONC, ',', 1), ',', -1) as value_deal1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(QRY.CONC, ',', 2), ',', -1) as value_deal2
FROM   (SELECT MONTH AS M, GROUP_CONCAT(value_deal) AS CONC
        FROM test1
        GROUP BY MONTH) QRY;

基本上使用group concat函数在一列中获取month的所有值，然后使用内置的字符串函数将该列拆分为多列.

Basically use the group concat function to get all the values for month in one column, and then split the column into multiple columns by using in built string functions.

这是演示

这篇关于在MySQL中创建视图并将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！