CODEWITHSUNDEEP
mysql
Ram Choubey
Ram Choubey

Reputation: 275

Changing the format of mysql table using mysql query

I have the following table in mysql database

enter image description here

i want to get the result in the following format enter image description here

Upvotes: 0

Views: 36

Answers (2)

nisha Sk
nisha Sk

Reputation: 20

You can use this query:

select
    customerName,
    coalesce(sum(case when month(`date`) = 1 then paidamount end), '-') as `January` ,
    coalesce(sum(case when month(`date`) = 2 then paidamount end), '-') as `February`,
    coalesce(sum(case when month(`date`) = 3 then paidamount end), '-') as `March`,
    coalesce(sum(case when month(`date`) = 4 then paidamount end), '-') as `April`,
    coalesce(sum(case when month(`date`) = 5 then paidamount end), '-') as `May`,
    coalesce(sum(case when month(`date`) = 6 then paidamount end), '-') as `June`,
    coalesce(sum(case when month(`date`) = 7 then paidamount end), '-') as `July`,
    coalesce(sum(case when month(`date`) = 8 then paidamount end), '-') as `August`,
    coalesce(sum(case when month(`date`) = 9 then paidamount end), '-') as `September`,
    coalesce(sum(case when month(`date`) = 10 then paidamount end), '-') as `October`,
    coalesce(sum(case when month(`date`) = 11 then paidamount end), '-') as `November`,
    coalesce(sum(case when month(`date`) = 12 then paidamount end), '-') as `December`
from test
group by customerName

Upvotes: 1

Blank
Blank

Reputation: 12378

This is a typical table pivot issue, try following:

select
    customerName,
    coalesce(max(case when month(`date`) = 1 then paidamount end), '-') as `January` ,
    coalesce(max(case when month(`date`) = 2 then paidamount end), '-') as `February`,
    coalesce(max(case when month(`date`) = 3 then paidamount end), '-') as `March`,
    coalesce(max(case when month(`date`) = 4 then paidamount end), '-') as `April`,
    coalesce(max(case when month(`date`) = 5 then paidamount end), '-') as `May`,
    coalesce(max(case when month(`date`) = 6 then paidamount end), '-') as `June`,
    coalesce(max(case when month(`date`) = 7 then paidamount end), '-') as `July`,
    coalesce(max(case when month(`date`) = 8 then paidamount end), '-') as `August`,
    coalesce(max(case when month(`date`) = 9 then paidamount end), '-') as `September`,
    coalesce(max(case when month(`date`) = 10 then paidamount end), '-') as `October`,
    coalesce(max(case when month(`date`) = 11 then paidamount end), '-') as `November`,
    coalesce(max(case when month(`date`) = 12 then paidamount end), '-') as `December`
from yourtable
group by customerName

Upvotes: 1

Related Questions