How to use SUM in a multi-return code?

Question

I did it in a code where it makes a sum of data entered in the months, but I don’t know how to make the AS become a array.

The Code I made:

DECLARE @I INT = 1;
WHILE @I < 12
BEGIN 
  SELECT SUM(valueEarn) AS monthValue1
    FROM earnAccount
   INNER JOIN bankAccount ON bankAccount.idBankAccount = 1
   WHERE MONTH(dateEarnCreate) = @I

  SET @I += 1
END

as it turned out:

As you can see it returns me the same column name monthValue1, wanted it to be generated from monthValue1 to monthValue12,pq then I will take this data to put on a graph.

wanted something like this:

Answer 1

To show them line by line simply remove the while and add a CASE verifying the month in question:

SELECT SUM(CASE MONTH(dateEarnCreate) WHEN 1 THEN valueEarn ELSE 0 END) AS monthValue1,
       SUM(CASE MONTH(dateEarnCreate) WHEN 2 THEN valueEarn ELSE 0 END) AS monthValue2,
       SUM(CASE MONTH(dateEarnCreate) WHEN 3 THEN valueEarn ELSE 0 END) AS monthValue3,
       SUM(CASE MONTH(dateEarnCreate) WHEN 4 THEN valueEarn ELSE 0 END) AS monthValue4
       SUM(CASE MONTH(dateEarnCreate) WHEN 5 THEN valueEarn ELSE 0 END) AS monthValue5,
       SUM(CASE MONTH(dateEarnCreate) WHEN 6 THEN valueEarn ELSE 0 END) AS monthValue6,
       SUM(CASE MONTH(dateEarnCreate) WHEN 7 THEN valueEarn ELSE 0 END) AS monthValue7,
       SUM(CASE MONTH(dateEarnCreate) WHEN 8 THEN valueEarn ELSE 0 END) AS monthValue8,
       SUM(CASE MONTH(dateEarnCreate) WHEN 9 THEN valueEarn ELSE 0 END) AS monthValue9,
       SUM(CASE MONTH(dateEarnCreate) WHEN 10 THEN valueEarn ELSE 0 END) AS monthValue10,
       SUM(CASE MONTH(dateEarnCreate) WHEN 11 THEN valueEarn ELSE 0 END) AS monthValue11,
       SUM(CASE MONTH(dateEarnCreate) WHEN 12 THEN valueEarn ELSE 0 END) AS monthValue12
  FROM earnAccount
 INNER JOIN bankAccount
    ON bankAccount.idBankAccount = 1