The whole process of the Cross report implementation

Business Description: Product Table      Product number Size (code)    color    quantity 1  38   black    1  38   Red   10 1  40   Black   50 2  38    Black   100 3  38   red   5 product Name Table    Product number product name 1   spider leather shoes 2    blue Spider shoes 3   black Widow leather shoes requirements show the report is as follows: Statistic report    size/color black    red 38  200 40   50  0 Build table: Create TABLE T_shoes (  p_id    number),   p_ Size  number (3),   p_color VARCHAR2 (6),   P_NUM   number (5)); Enter the test data as follows: Select p_id, P_size, P_color, p_num from t_shoes;   p_id p_size p_color p_num 1 38 Black 100 1 38 Red 10 1 4 0 Black 50 2 38 black 100 3 38 red 5   Because the result set is passed through the stored procedure, create the result statistic report: Created table T_shoes_report (  p_size &nbsp ; Number (3),   p_color VARCHAR2 (6),   P_NUM   number (5); Create a view to provide matching of all colors and dimensions (using the Cartesian product)    Create or Replace view v_all_products as SELECT distinct all_size.p_size , All_color.p_color from (select distinct t.p_size to t_shoes T) all_size, (select distinct t.p_color from T_shoes T) al L_color completes the following stored procedures by progressively printing: Create or replace Procedure  sp_report   as   str1 number (3) ;   STR2 varchar2 (30000); Begin    Delete from T_shoes_report;    for all_size in (select distinct p_size from v_all_products)    loop     &nbsp ;    --dbms_output.put_line (all_size.p_size);         for All_color in (select distinct P_color from v_all_products where p_size = all_size.p_size)       & nbsp;  loop           --dbms_output.put_line (All_ size.p_size| | ', ' | | All_color.p_color);           --Statistics            select NVL (sum (t.p_num), 0) into str1 from T_ Shoes t            where t.p_size = all_size.p_size            and t.p_color  = all_color.p_color;            --str2: = all_size.p_size| | ', ' | | all_color.p_color| | ', ' | | str1;           --dbms_output.put_line (str2);            INSERT INTO T_shoes_report (p_size,p_color,p_num)             values (ALL_SIZE.P_SIZE,ALL_COLOR.P_COLOR,STR1);         end loop;      End loop;         commit;       End;     test:     SQL > EXEcute Sp_report;    pl/sql procedure successfully completed     SQL> SELECT * FROM t_shoes_report;     p_size p_color  p_num     Table A---- ---------------         38 Black           200         38 Red            15         40 Black            50         40 Red             0     The above data, you can do the following methods of report display      report       size/color black and red 38 200 15 40 50 0 1, through Java program to convert table a data HTML code, display as Table b     2, through the SQL statement transformation   &NB sp;  select        t.p_size dimensions,      sum (DECode (T.p_color, ' black ', t.p_num)) Black,      sum (decode (t.p_color, ' Red ', t.p_num)) Red       from T_shoes_report t   GROUP by t.p_size        results:    & nbsp Size black and black 38 200 15 40 50 0