crosstab query - 2 level

(2 posts) (2 voices)

Tags:

  1. ajdjackson, Member

    Hi

    I was wondering if it's possible to create a two-level crosstab report.

    What I am trying to do is to create a crosstab report that groups by the Year and the month - something like this:

    2016 2017 2018
    Jan Feb Mar Apr May Jun.... Jan Feb Mar Apr May Jun.... Jan Feb Mar Apr May Jun....

    Any thoughts?

    Jake

  2. myDBR Team, Key Master

    For now easiest is just to use normal crosstab and include the year for January column:

    select
    Name,
    concat('dbr.html:', if (m=1, y, ''), '
    ', substr(monthname(str_to_date(m, '%m')), 1, 3 )) as 'Month',
    sum(value) as ''
    from mydata
    group by 1, 2
    order by y, m;

    where y=year and m=month number

    --
    myDBR Team


Reply

You must log in to post.