1

I have a table; Table A.

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

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

Community
  • 1
  • 1
Luthfi Pratama Yogaswara
  • 37
  • 2
  • Why do you want this to be a view? Generally, issues of data display are best resolved in application level code, if that's available – Strawberry Dec 16 '16 at 08:11
  • 1
    Also, in the absence of any PRIMARY KEY, how do you know one is deal1 and which is deal2 – Strawberry Dec 16 '16 at 08:14

1 Answers1

0

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;

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.

Here is a demo

ivanm
  • 138
  • 1
  • 8