Home>

When outputting a table like the image below, I would like to output it in SQL instead of doing a muddy work in Excel.
Thanks for your teaching.

<DDL>
CREATE TABLE Bunseki
(
 nengetsu CHAR (6) NOT NULL,
 name CHAR (3) NOT NULL,
 rank1 CHAR (2) NOT NULL,
 rank2 CHAR (2),
 kosu number);
<INSERT>
INSERT INTO Bunseki VALUES ('201904', 'AAA', '01', '04', 1);
INSERT INTO Bunseki VALUES ('201904', 'AAA', '02', '04', 2);
INSERT INTO Bunseki VALUES ('201904', 'AAA', '03', '04', 3);
INSERT INTO Bunseki VALUES ('201904', 'AAA', '04', '04', 3);
INSERT INTO Bunseki VALUES ('201905', 'AAA', '05', '09', 4);
INSERT INTO Bunseki VALUES ('201905', 'AAA', '06', '09', 5);
INSERT INTO Bunseki VALUES ('201905', 'AAA', '07', '09', 6);
INSERT INTO Bunseki VALUES ('201905', 'AAA', '08', '09', 7);



SELECT
     nengetsu
    , name
    , rank1
    , rank2
    , sum (kosu) AS KOSU
FROM
    Bunseki
GROUP BY
    GROUPING SETS ((nengetsu, name, rank1), (nengetsu, name, rank2))
ORDER BY
    nengetsu, name, rank1, rank2
  • Answer # 1

    Execute it with sqlplus, for the time being possible with SQL.
    To bring the rank total to the right, you need PL/SQL that can be edited more freely.

    SQL>COLUMN KOSU HEADING "Rank"
    SQL>COLUMN rank1_l FORMAT A11 HEADING "Rank 1 quantity"
    SQL>COLUMN rank2_l FORMAT A11 HEADING "2 ranks"
    SQL>COLUMN rank1_r FORMAT A11 HEADING "Rank 1 quantity"
    SQL>COLUMN rank2_r FORMAT A11 HEADING "Rank 2 quantity"
    SQL>SELECT
      2 sum (kosu) AS KOSU
      3, case when nengetsu = '201904' then rank1 end as rank1_l
      4, case when nengetsu = '201904' then rank2 end as rank2_l
      5, case when nengetsu = '201905' then rank1 end as rank1_r
      6, case when nengetsu = '201905' then rank2 end as rank2_r
      7 FROM
      8 Bunseki
      9 GROUP BY
     10 GROUPING SETS ((nengetsu, name, rank1), (nengetsu, name, rank2))
     11 ORDER BY
     12 nengetsu, name, rank1, rank2;
        Rank Rank 1 piece Rank 2 piece Rank 1 piece Rank 2 piece
    ---------- ----------- ----------- ----------- ------- ----
             1 01
             2 02
             3 03
             3 04
             9 04
             4 05
             5 06
             6 07
             7 08
            22 09
    10 rows were selected.