CODEWITHSUNDEEP
mysqlsql
alien250
alien250

Reputation: 49

Transpose row to columns in SQL/PHP

I fetched the following data:

MONTH      |      TOTAL
-------------------------
Jan        |      100
Feb        |      200
Mar        |      300

Using this query:

$query = "SELECT DATE_FORMAT(date,'%b') AS MONTH, SUM(col1+col2) AS TOTAL FROM myTable GROUP BY YEAR(date),MONTH(date)";

How can I edit the above query or re-write to get the following result:

JAN | FEB | MAR
-------------------------
100 | 200 | 300

I have gone through almost all the the other similar posts. However, sql transposing, to me, is very confusing. Any input is much appreciated!

Upvotes: 2

Views: 236

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You can use conditional aggregation. The following will work in either SQL Server or MySQL:

select year(date),
       sum(case when month(date) = 1 then col1 + col2 else 0 end) as jan,
       sum(case when month(date) = 2 then col1 + col2 else 0 end) as feb,
       sum(case when month(date) = 3 then col1 + col2 else 0 end) as mar
from mytable
group by year(date)
order by year(date);

EDIT (regarding the comment):

select year(date),
       sum(case when month(date) = 1 then val else 0 end) as jan,
       sum(case when month(date) = 2 then val else 0 end) as feb,
       sum(case when month(date) = 3 then val else 0 end) as mar
from (select t.*, (col1 + col2) as val
      from mytable
     ) t
group by year(date)
order by year(date);

Upvotes: 2

Related Questions