如何对uniform_size表数据分组统计，实现指定格式输出？

需要对uniform_size表的数据进行分组展示，现有表数据如下：

ROLL_NO SHOE_NO SHOCKS  CAP SHIRT   TROUSER JACKET
                        
21  7   12  32  28  22  32
22  7   15  30  22  12  22
23  8   16  31  21  14  20
24  9   17  33  28  19  26
25  7   16  30  22  12  22
26  7   15  31  22  12  22
27  8   15  30  22  12  22
28  7   17  30  22  12  22
29  8   15  30      12  22
31  8   15  30  22  12  

希望得到如下格式的统计结果：

ITEM    SIZE    COUNT
SHOE    7   5
SHOE    8   4
SHOE    9   1
SHOCKS  12  1
SHOCKS  15  5
SHOCKS  16  2
SHOCKS  17  2
CAP 32  1
CAP 30  6
CAP 31  2
CAP 33  1
SHIRT   28  2
SHIRT   22  6
SHIRT   21  1
SHIRT   NULL    1
TROUSER 22  1
TROUSER 12  7
TROUSER 14  1
TROUSER 19  1
JACKET  32  1
JACKET  22  6
JACKET  20  1
JACKET  26  1
JACKET  NULL    1

建表及插入语句如下：

create table uniform_size (
    ROLL_NO NUMBER UNIQUE,
    SHOE_NO NUMBER,
    SHOCKS NUMBER,
    CAP NUMBER,
    SHIRT NUMBER,
    TROUSER number,
    JACKET NUMBER
);
INSERT INTO UNIFORM_SIZE VALUES ( 21,7,12,32,28,22,32);
INSERT INTO UNIFORM_SIZE VALUES ( 22,7,15,30,22,12,22);
INSERT INTO UNIFORM_SIZE VALUES ( 23,8,16,31,21,14,20);
INSERT INTO UNIFORM_SIZE VALUES ( 24,9,17,33,28,19,26);
INSERT INTO UNIFORM_SIZE VALUES ( 25,7,16,30,22,12,22);
INSERT INTO UNIFORM_SIZE VALUES ( 26,7,15,31,22,12,22);
INSERT INTO UNIFORM_SIZE VALUES ( 27,8,15,30,22,12,22);
INSERT INTO UNIFORM_SIZE VALUES ( 28,7,17,30,22,12,22);
INSERT INTO UNIFORM_SIZE VALUES ( 29,8,15,30,NULL,12,22);
INSERT INTO UNIFORM_SIZE VALUES ( 31,8,15,30,22,12,NULL);
INSERT INTO UNIFORM_SIZE VALUES ( 32,NULL,15,30,22,12,23);
INSERT INTO UNIFORM_SIZE VALUES ( 33,NULL,15,31,22,12,23);
INSERT INTO UNIFORM_SIZE VALUES ( 34,9,NULL,30,22,12,23);
INSERT INTO UNIFORM_SIZE VALUES ( 35,9,18,31,22,12,23);
INSERT INTO UNIFORM_SIZE VALUES ( 36,9,NULL,30,28,12,23);
INSERT INTO UNIFORM_SIZE VALUES ( 37,9,18,30,22,12,24);
INSERT INTO UNIFORM_SIZE VALUES ( 38,10,19,30,22,12,24);
INSERT INTO UNIFORM_SIZE VALUES ( 39,10,19,30,22,14,24);
INSERT INTO UNIFORM_SIZE VALUES ( 40,NULL,NULL,NULL,NULL,NULL,NULL);

尝试过分组方法但未得到预期结果，求解决方案。

要实现这种列转行后分组统计的需求，核心是把每类服饰的列数据转换为行数据，再按服饰类型和尺码分组计数。用UNION ALL拆分各列后分组统计即可，同时会自动保留NULL值的统计结果。

执行以下SQL语句即可得到预期结果：

SELECT 'SHOE' AS ITEM, SHOE_NO AS SIZE, COUNT(*) AS COUNT
FROM uniform_size
GROUP BY SHOE_NO
UNION ALL
SELECT 'SHOCKS' AS ITEM, SHOCKS AS SIZE, COUNT(*) AS COUNT
FROM uniform_size
GROUP BY SHOCKS
UNION ALL
SELECT 'CAP' AS ITEM, CAP AS SIZE, COUNT(*) AS COUNT
FROM uniform_size
GROUP BY CAP
UNION ALL
SELECT 'SHIRT' AS ITEM, SHIRT AS SIZE, COUNT(*) AS COUNT
FROM uniform_size
GROUP BY SHIRT
UNION ALL
SELECT 'TROUSER' AS ITEM, TROUSER AS SIZE, COUNT(*) AS COUNT
FROM uniform_size
GROUP BY TROUSER
UNION ALL
SELECT 'JACKET' AS ITEM, JACKET AS SIZE, COUNT(*) AS COUNT
FROM uniform_size
GROUP BY JACKET
ORDER BY ITEM, SIZE NULLS LAST;

关键说明： #

1. 列转行处理：通过UNION ALL将每个服饰列（如SHOE_NO）单独查询，指定对应的ITEM名称（如'SHOE'），把列结构的数据转为行结构。
2. 分组计数：每个子查询按服饰尺码分组，用COUNT(*)统计该尺码的出现次数，包括NULL值的情况（GROUP BY默认会将NULL作为一个分组）。
3. 结果排序：最后用ORDER BY按服饰类型排序，同时让NULL值排在末尾（NULLS LAST），让结果更规整。

内容的提问来源于stack exchange，提问作者Jay