0

I have a table as this, name aaa:

year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1993   4     2.4

I want a result like this:

year m1   m2   m3   m4
1991 1.1  1.2  1.3  1.4
1992 2.1  2.2  2.3  2.4

As a novice, I can't find a way out after hours searching and experiencing. I tried this but failed:

SELECT year,
(SELECT amount FROM aaa WHERE month=1) AS m1,
(SELECT amount FROM aaa WHERE month=2) AS m2,
(SELECT amount FROM aaa WHERE month=3) AS m3,
(SELECT amount FROM aaa WHERE month=4) AS m4
FROM aaa GROUP BY year;

Anyone could show me? or let me know where I can find an answer? Thx in advance.

or this:

SELECT year,
(SELECT amount FROM aaa m WHERE month=1 AND m.year=aaa.year) AS m1,
(SELECT amount FROM aaa m WHERE month=2 AND m.year=aaa.year) AS m2,
(SELECT amount FROM aaa m WHERE month=3 AND m.year=aaa.year) AS m3,
(SELECT amount FROM aaa m WHERE month=4 AND m.year=aaa.year) AS m4
FROM aaa GROUP BY year;
Sean.H
  • 640
  • 1
  • 6
  • 18
  • Seriously consider handling issues of data display in application code. – Strawberry May 14 '18 at 16:48
  • Solved the problem by GROUP_CONCAT & CASE SYNTAX like this: ' GROUP_CONCAT( DISTINCT CASE WHEN month=2 THEN aaa.amount ELSE NULL END) AS m2' – Sean.H May 15 '18 at 15:07

0 Answers0