Home>

oracle - create sql to realize attached table output

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
sql oracle pl/sql
  • 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.

Related articles