La query √® un esempio di come mostrare righe + riepilogo senza usare la funzionalit√† CUBE (sfrutta l’order by per creare campi di rottura)

(select concat(LPad(rp.CDGRM, 2, '0'),
              LPad(g.CDSET, 2, '0'),
              LPad(g.CDINT, 6, '0'),
              g.PREZZATURA) as BR,
       0 AS MTLEVEL,
       r.STRMERC,
       r.DESREF,
       g.CDSET,
       g.CDINT,
       g.PREZZATURA,
       rp.DESCR,
       rp.CALIBRO,
       g.NRGIAC,
       g.VARIETA,
       g.ORIGINE,
       o.DSORIG,
       g.CDCONF,
       i.DSCONF,
       g.CDFOR,
       LEFT(f.RAGSOC, 17) RAGSOC,
       g.QTA,
       g.COLLI,
       g.PSMEDIO,
       g.BLOCCO,
       rp.CATEGORIA,
       g.COSTO,
       (g.QTA * g.COSTO) as VALORE,
       Concat(mid(g.DATAINS, 7, 2),
              '/',
              mid(g.DATAINS, 5, 2),
              '/',
              mid(g.DATAINS, 3, 2)) as INSERIMENTO
  from of_giacenze as g
  left join of_refepiat as rp
    on rp.codsoc =3D 30
   and rp.codset =3D g.cdset
   and rp.codint =3D g.cdint
  left join referenze as r
    on r.codset =3D rp.codset
   and r.codint =3D rp.codint
  left join of_origini as o
    on o.cdorig =3D g.origine
  left join of_imba as i on i.cdconf =3D g.cdconf
  left join fornitori as f on f.codfor =3D g.cdfor
 where g.dplog =3D 95
   and g.CDSET between 50 and 50
   and g.CDINT between 1 and 9999999999
   and rp.CDGRM between 26 and 26)
union all (select concat(LPad(rp.CDGRM, 2, '0'),
                         LPad(g.CDSET, 2, '0'),
                         LPad(g.CDINT, 6, '0'),
                         g.PREZZATURA) as BR,
                  2 AS MTLEVEL,
                  r.STRMERC,
                  ' ' as DESREF,
                  ' ' as CDSET,
                  ' ' as CDINT,
                  ' ' as PREZZATURA,
                  ' ' as DESCR,
                  ' ' as CALIBRO,
                  ' ' as NRGIAC,
                  ' ' as VARIETA,
                  ' ' as ORIGINE,
                  ' ' as DSORIG,
                  'TOT.' as CDCONF,
                  ' ' as DSCONF,
                  ' ' as CDFOR,
                  ' ' as RAGSOC,
                  sum(g.QTA) as QTA,
                  sum(g.COLLI) as COLLI,
                  ' ' as PSMEDIO,
                  '-' as BLOCCO,
                  ' ' as CATEGORIA,
                  ' ' as COSTO,
                  SUM(g.QTA * g.COSTO) as VALORE,
                  '' as INSERIMENTO
             from of_giacenze as g
             left join of_refepiat as rp
               on rp.codsoc =3D 30
              and rp.codset =3D g.cdset
              and rp.codint =3D g.cdint
             left join referenze as r
               on r.codset =3D rp.codset
              and r.codint =3D rp.codint
             left join of_origini as o on o.cdorig =3D g.origine
             left join fornitori as f on f.codfor =3D g.cdfor
            where g.dplog =3D 95
              and g.CDSET between 50 and 50
              and g.CDINT between 1 and 99999999
              and rp.CDGRM between 26 and 26
            group by MTLEVEL, r.STRMERC, rp.DESCR, g.CDSET, g.CDINT, g.PREZZATURA, 
                     rp.DESCR, BR, NRGIAC,
                     VARIETA, ORIGINE, DSORIG, CDFOR, RAGSOC, PSMEDIO, BLOCCO)
union all (select '99999999999999999' as BR,
                  3 AS MTLEVEL,
                  ' ' as STRMERC,
                  ' ' as DESREF,
                  ' ' as CDSET,
                  ' ' as CDINT,
                  ' ' as PREZZATURA,
                  ' ' as DESCR,
                  ' ' as CALIBRO,
                  ' ' as NRGIAC,
                  ' ' as VARIETA,
                  ' ' as ORIGINE,
                  ' ' as DSORIG,
                  'TOT.' as CDCONF,
                  ' ' as DSCONF,
                  ' ' as CDFOR,
                  ' ' as RAGSOC,
                  sum(g.QTA) as QTA,
                  sum(g.COLLI) as COLLI,
                  ' ' as PSMEDIO,
                  '-' as BLOCCO,
                  ' ' as CATEGORIA,
                  ' ' as COSTO,
                  SUM(g.QTA * g.COSTO) as VALORE,
                  '' as INSERIMENTO
             from of_giacenze as g
            where g.dplog =3D 95
            group by DPLOG)
 order by br, mtlevel, nrgiac