SQL Donut

Oracle version of http://code.openark.org/blog/mysql/sql-pie-chart. Just change the val query to greate your own donuts.

######
Note 17.8.2009 wm_concat does not quarantee the right col_number order. Update to this post coming in future.
Note 2.9.2009 replaced wm_concat implementation to listagg. New in 11.2
######

:s size
:r radius
:t stretch

with val as (
select 'red' name_column, 1 value_column from dual 
union all
select 'blue' name_column, 2 value_column from dual 
union all
select 'orange' name_column, 3 value_column from dual 
union all
select 'white' name_column, 4 value_column from dual 
), ratio as (
select row_number() over (order by v.value_column,rownum) name_order
     , v.name_column
     , v.value_column
     , sum(value_column) over (order by value_column,rownum) accumulating_value
     , sum(value_column) over (order by value_column,rownum) 
            / sum(value_column) over () accumulating_value_ratio
     , 2*3.15*sum(value_column) over (order by value_column,rownum) 
            / sum(value_column) over () accumulating_value_radians
     , trunc(100*value_column / sum(value_column) over (),2) pc
  from val v
), t1 as (
select level-1 value from dual connect by level <= to_number(:s)*to_number(:t)
), t2 as (
select level-1 value from dual connect by level <= :s
), c as (
select '#;o:X"@+-=123456789abcdef' as colors from dual
), ro as (
select t1.value col_number
     , t2.value row_number
     , t1.value/:t - (:s-1)/2 dx
     , (:s-1)/2 - t2.value dy
  from t1, t2
)
, ro2 as (
select ro.*, case when dx = 0 
                  then 3.14/2 
                  else (atan(abs(dy/dx))) 
              end abs_radians
  from ro)
, ro3 as (
select ro2.*, case when sign(dy) >= 0 and sign(dx) >=0 then abs_radians
                   when sign(dy) >= 0 and sign(dx) <=0 then 3.14-abs_radians  
                   when sign(dy) <= 0 and sign(dx) <=0 then 3.14+abs_radians  
                   when sign(dy) <= 0 and sign(dx) >=0 then 2*3.14-abs_radians  
               end radians 
  from ro2
) 
select listagg(case when round(sqrt(power(col_number/:t-0.5-(:s-1)/2, 2) 
                       + power(row_number-(:s-1)/2, 2))) BETWEEN :r/2 AND :r
                    then rpad(name_order,:t,name_order)
                    else rpad(' ',:t,' ')
                    end) within group (order by col_number) circle
  from (select substr(colors, name_order, 1) name_order
       , row_number
       , col_number
       , row_number() over (partition by col_number,row_number 
                                order by name_order) c
    from ratio, ro3, c
   where accumulating_value_radians >= radians 
) where c = 1
group by row_number
union all
select rpad(substr(colors,name_order,1),:t,substr(colors,name_order,1))||' '||
       name_column||' '||
       value_column||' ('|| 
       pc||'%)'
  from ratio, c
;


CIRCLE
        oooooo;;;;;;;;      
    oooooooooo;;;;;;;;;;;;  
  oooooooo          ;;######
  oooooooo          ########
  oooooooo          ::::::::
    oo::::::::::::::::::::  
        ::::::::::::::      
## red 1 (10%)
;; blue 2 (20%)
oo orange 3 (30%)
:: white 4 (40%)