Oracle数据库交叉表查询：按设备类型与站点统计数量

当然可以实现，Oracle提供了多种方式来完成这种行转列的统计需求，以下是几种常用方案：

1. 静态列转换（已知所有站点） #

如果站点列表是固定的（比如已知只有site1、site2、site3），可以直接使用PIVOT子句实现，同时用NVL将空值替换为0：

假设你的原始表名为device_stats，包含device_type（设备类型）和site（站点）两列，查询语句如下：

SELECT device_type,
       NVL(site1, 0) AS site1,
       NVL(site2, 0) AS site2,
       NVL(site3, 0) AS site3
FROM (
    -- 子查询获取原始数据
    SELECT device_type, site
    FROM device_stats
)
PIVOT (
    -- 统计每个(设备类型,站点)组合的数量
    COUNT(*) FOR site IN ('site1' AS site1, 'site2' AS site2, 'site3' AS site3)
)
ORDER BY device_type;

说明： #

• PIVOT子句会将site列的不同值转换为列名，COUNT(*)用于统计对应组合的记录数
• NVL函数将没有数据的单元格（NULL）替换为0，匹配你期望的展示效果

2. 动态列转换（站点不固定/可新增） #

如果站点数量不确定或可能新增，静态SQL无法适配，此时可以用动态SQL结合LISTAGG函数自动生成列：

DECLARE
    v_full_sql VARCHAR2(4000);
    v_site_cols VARCHAR2(4000);
BEGIN
    -- 第一步：拼接所有站点的PIVOT列定义
    SELECT LISTAGG('''' || site || ''' AS ' || site, ', ') WITHIN GROUP (ORDER BY site)
    INTO v_site_cols
    FROM (SELECT DISTINCT site FROM device_stats);

    -- 第二步：拼接完整的查询语句，包含NVL处理空值
    v_full_sql := 'SELECT device_type, ' ||
                  LISTAGG('NVL(' || site || ', 0) AS ' || site, ', ') WITHIN GROUP (ORDER BY site) ||
                  ' FROM (SELECT device_type, site FROM device_stats) ' ||
                  'PIVOT (COUNT(*) FOR site IN (' || v_site_cols || ')) ' ||
                  'ORDER BY device_type';

    -- 输出生成的SQL（可直接执行或复制到客户端运行）
    DBMS_OUTPUT.PUT_LINE(v_full_sql);
    -- 如需直接执行，取消下面的注释：
    -- EXECUTE IMMEDIATE v_full_sql;
END;
/

说明： #

• LISTAGG函数将所有不同的站点拼接成PIVOT需要的格式
• 动态生成的SQL会自动适配所有现存站点，新增站点后重新执行即可更新结果

3. 兼容低版本Oracle（11g之前） #

如果你的Oracle版本低于11g（不支持PIVOT），可以用CASE语句结合GROUP BY实现：

SELECT device_type,
       COUNT(CASE WHEN site = 'site1' THEN 1 END) AS site1,
       COUNT(CASE WHEN site = 'site2' THEN 1 END) AS site2,
       COUNT(CASE WHEN site = 'site3' THEN 1 END) AS site3
FROM device_stats
GROUP BY device_type
ORDER BY device_type;

说明： #

• CASE语句仅在匹配对应站点时返回1，否则返回NULL
• COUNT函数会忽略NULL值，因此没有数据的站点会自动统计为0

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