0

My table has data in the following format; where period refers to a given month of the year.

   // table 
   id name location type period     value
   1  foo  bar      baz  2021-01-01 500
   2  foo  bar      baz  2021-02-01 1500
   3  foo  bar      baz  2021-03-01 1500
   ...

   // desired output
   id  name location  type MonthJan  MonthFeb MonthMar
   1   foo  bar       baz  500       1500     1500
   ...

And I (think) I need to pivot the results to show any given 12 months of data. Having searched Stackoverflow, have hashed together this which is a "semi-dynamic" pivot in my eyes. It seems to work but looks a bit simple/clunky.

The end goal is to get the sum of amount for each period

select name, location, type,
  sum(case when MONTH(period) = '1' then amount else 0 end) MonthJan,
  sum(case when MONTH(period) = '2' then amount else 0 end) MonthFeb,
  sum(case when MONTH(period) = '3' then amount else 0 end) MonthMar,
  ...
from myTable
where period >= '2021-01-01' and period <= '2021-12-31' ## 1 calendar year 
group by name, location, type

It was my intention then to let the user pass in conditions to "period" to then return say all data between 1st Feb 2021 - 31st Jan 2022 - essentially 12 months.

This sort of works but if they wanted to see data for 2 years it soon falls flat on its face (I'd need 24 columns then not just 12).

I've looked at dynamic queries - it seems like my approach is just wrong - is this workable or does it need a rethink?

I'm currently using MySQL which I understand doesn't have a pivot function, but the solution needs to be as flexible as possible (i.e. work with SQL server) hence my looking at this SUM/case approach.

user4681670
  • 113
  • 5
  • 2
    I would suggest restructuring the data in the application, rather than in the query. – Gordon Linoff Feb 28 '21 at 14:16
  • 2
    Often this type of thing is best handled in the "presentation layer". I don't know how this information is being presented to the end user, but my guess is that there is something between the query output and them, even if it's just an Excel spreadsheet. – Steve Lovell Feb 28 '21 at 14:16
  • You would be correct - this is purely for the user's visual benefit at this stage - it's basically just being printed into an HTML table; I figured there may be an easy way to do this in SQL so the results could just be iterated out line by line. – user4681670 Feb 28 '21 at 14:27
  • What's your MySQL version? – Kazi Mohammad Ali Nur Romel Feb 28 '21 at 14:32
  • 1
    Does this answer your question? [MySQL pivot table query with dynamic columns](https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) – astentx Feb 28 '21 at 15:02

0 Answers0