8-33 count(*)で棒グラフを作成

SQLパズル

DTable
DNo  Name
---  ----
 10  AAAA
 10  BBBB
 10  CCCC
 20  DDDD
 20  EEEE
 20  FFFF
 20  GGGG
 20  HHHH
 30  IIII
 30  JJJJ
 30  KKKK
 30  LLLL
 30  MMMM
 30  NNNN

DNoのそれぞれの件数で
下記の棒グラフを作成する。

出力結果
D10   D20   D30
----  ----  ----
null  null  *
null  *     *
null  *     *
*     *     *
*     *     *
*     *     *

SQLクックブックのレシピ12.10を参考にさせていただきました

データ作成スクリプト

create table DTable(DNo,Name) as
select 10,'AAAA' from dual union
select 10,'BBBB' from dual union
select 10,'CCCC' from dual union
select 20,'DDDD' from dual union
select 20,'EEEE' from dual union
select 20,'FFFF' from dual union
select 20,'GGGG' from dual union
select 20,'HHHH' from dual union
select 30,'IIII' from dual union
select 30,'JJJJ' from dual union
select 30,'KKKK' from dual union
select 30,'LLLL' from dual union
select 30,'MMMM' from dual union
select 30,'NNNN' from dual;

SQL

col D10 for a5
col D20 for a5
col D30 for a5

--■■■クロスジョインを使う方法■■■
select case when b.Counter <= a.D10 then '*' end as D10,
       case when b.Counter <= a.D20 then '*' end as D20,
       case when b.Counter <= a.D30 then '*' end as D30
  from (select count(decode(DNo,10,1)) as D10,
               count(decode(DNo,20,1)) as D20,
               count(decode(DNo,30,1)) as D30
          from DTable) a,(select RowNum as Counter from all_catalog) b
 where b.Counter <= any(a.D10,a.D20,a.D30)
order by b.Counter desc;

--■■■グループ化する方法■■■
select max(decode(DNo,10,'*')) as D10,
       max(decode(DNo,20,'*')) as D20,
       max(decode(DNo,30,'*')) as D30
from (select DNo,
      Row_Number() over(partition by DNo order by Name) as rn
      from DTable)
 group by rn
order by D10 desc,D20 desc,D30 desc;

解説

any述語を結合条件に使っています。(greatest関数を使ってもいいですが)

---