SQL Pivot with multiple lines per result

Question

I'm trying to learn how I can pivot date with Advantage Database Server.

I have the following code which will pivot (using CASE), our monthly sales data:

    SELECT YEAR(date) as MyYear
     , SUM(CASE WHEN MONTH(date) =  1 THEN value END) jan_revenue
     , SUM(CASE WHEN MONTH(date) =  1 THEN value - (qty*ekp/pe) END) jan_margin
     , SUM(CASE WHEN MONTH(date) =  2 THEN value END) feb_revenue
     , SUM(CASE WHEN MONTH(date) =  2 THEN value - (qty*ekp/pe) END) feb_margin
     , SUM(CASE WHEN MONTH(date) =  3 THEN value END) mar_revenue
     , SUM(CASE WHEN MONTH(date) =  3 THEN value - (qty*ekp/pe) END) mar_margin
  FROM (SELECT DAB020.*
             , EXTRACT(YEAR  FROM date) year
             , EXTRACT(MONTH FROM date) month
       FROM "DAB020.ADT" DAB020
          where buart = 'A' and auf_kumsta = 'J' and date >= '2021-01-01'
       ) mysalesdata
 GROUP BY YEAR(date)

This produces a result looking like this:

MyYear  jan_revenue jan_margin  feb_revenue jan_margin_1    mar_revenue jan_margin_2
2021    23123414    12312323    4554535     545345          435345      123123

What I really want, is for the revenue and margin to be on different lines:

MyYear  TYPE    jan         Feb         Mar 
2021    Revenue 23123414    12312323    4554535    
2021    Margin  545345      435345      123123

(numbers are fake-but you get the idea)

How can I do this? I'm sure that a combination of CASE and Grouping is the solution, but I am not able to work it out.

(edit: there is no pivot function in ADS, as far as I know)

Answer 1

Unpivot the data to get the margin and revenue in different rows. Then aggregate like you are doing now:

SELECT YEAR(date) as MyYear, type, 
       SUM(CASE WHEN MONTH(date) =  1 THEN value END) as jan,
       SUM(CASE WHEN MONTH(date) =  2 THEN value END) as feb,
       SUM(CASE WHEN MONTH(date) =  3 THEN value END) as mar
FROM (SELECT DAB020.*
      FROM (SELECT date, 'revenue' as type, value, buart, aufkumsta
            FROM "DAB020.ADT" UNION ALL
            SELECT date, 'margin' as type, value - (qty*ekp/pe), buart, aufkumsta
            FROM "DAB020.ADT"
           ) DAB020
      WHERE buart = 'A' AND auf_kumsta = 'J' AND
            date >= '2021-01-01'
     ) mysalesdata
 GROUP BY YEAR(date)