0

My table look like this

name    value   monthyear   year

g1     10   March 18    2018
g1     11   March 18    2018
g1     34   March 19    2019
g1     45   March 19    2019
g2     10   April 18    2018
g1     11   May 18      2018
g1     34   May 19      2019
g1     45   June 19     2019

And I need out put like this

Name    March 18    March 19    April 18    May 18      May 19  June 19
g1      21          79                       11           34      45
g2                                  10

In Pivot table I am getting this, How can I get this table with sql query ? Please help & Thanks in advance.

Gopipuli
  • 393
  • 1
  • 12
  • 37
  • https://stackoverflow.com/questions/62172565/mysql-pivot-like-operation-to-get-breakdown-of-percentage-of-total-events-per-da –  Jun 05 '20 at 07:52
  • 1
    Pivot your data on the client side using its report subsystem. – Akina Jun 05 '20 at 07:57
  • SELECT name, monthyear, sum(value) FROM fullile GROUP BY name, monthyear I tried this but this gives all months in a single column @0xN0 – Gopipuli Jun 05 '20 at 07:57
  • *but this gives all months in a single column* Well, this is the result which 'd sent to client. Which 'd draw it as pivot table. – Akina Jun 05 '20 at 07:59
  • Just do what the guy who answered me did. https://stackoverflow.com/a/62172611/13361518 It generates the exact kind of query the answer suggested here does, except it does not require you to explicitly specify the range of possible values. –  Jun 05 '20 at 08:02

1 Answers1

1

You can use case expression inside sum. here is the demo.

select
    name,
    sum(case when monthyear = 'March 18' then value end) as March18
    sum(case when monthyear = 'March 19' then value end) as March19
    ..
    ..
from yourTable
group by
    name

output:

| name | March_18 | March_19 | April_18 | May_18 | May_19 | June_19 |
| ---- | -------- | -------- | -------- | ------ | ------ | ------- |
| g1   | 21       | 79       |          | 11     | 34     |         |
| g2   |          |          | 10       |        |        |         |
zealous
  • 7,336
  • 4
  • 16
  • 36