sql - Oracle:将(合并)一些计数到单行上？

Question

更新:我所说的coalesce 我应该叫pivot。

我正在从日志表中提取一些日常使用计数。我可以轻松地为每个日期/项目获取一行数据，但我想将列合并为一行。

例如，我有:

date    item-to-be-counted count-of-item
10/1    foo                23
10/1    bar                45
10/2    foo                67
10/2    bar                89

我想要:

date    count-of-foo     count-of-bar
10/1    23               45
10/2    67               89

这是我当前的 10g 查询。

select    trunc(started,'HH'),depot,count(*)
  from    logstats
 group by trunc(started,'HH'),depot
 order by trunc(started,'HH'),depot;

TRUNC(STARTED,'HH')       DEPOT      COUNT(*)
------------------------- ---------- --------
10/01/11 01.00.00         foo        28092
10/01/11 01.00.00         bar        2194
10/01/11 02.00.00         foo        3402
10/01/11 02.00.00         bar        1058

更新:11g 有一个枢轴操作。接受的答案显示了如何在 9i 和 10g 中执行此操作。

Answer 1

您正在寻找的是旋转 - 将行数据转换为列数据。

Oracle 9i+，使用 WITH/CTE:

用:

WITH summary AS (
    SELECT TRUNC(ls.started,'HH') AS dt,
           ls.depot,
           COUNT(*) AS num_depot
      FROM logstats ls
  GROUP BY TRUNC(ls.started,'HH'), ls.depot)
  SELECT s.dt,
         MAX(CASE WHEN s.depot = 'foo' THEN s.num_depot ELSE 0 END) AS "count_of_foo",
         MAX(CASE WHEN s.depot = 'bar' THEN s.num_depot ELSE 0 END) AS "count_of_bar"
    FROM summary s
GROUP BY s.dt
ORDER BY s.dt

非与/CTE 等效

用:

  SELECT s.dt,
         MAX(CASE WHEN s.depot = 'foo' THEN s.num_depot ELSE 0 END) AS "count_of_foo",
         MAX(CASE WHEN s.depot = 'bar' THEN s.num_depot ELSE 0 END) AS "count_of_bar"
    FROM (SELECT TRUNC(ls.started,'HH') AS dt,
                 ls.depot,
                 COUNT(*) AS num_depot
            FROM LOGSTATS ls
        GROUP BY TRUNC(ls.started, 'HH'), ls.depot) s
GROUP BY s.dt
ORDER BY s.dt

Oracle9i 之前的版本需要 CASE声明更改为 DECODE ，Oracle特有的IF/ELSE逻辑。

Oracle 11g+，使用 PIVOT

未经测试:

  SELECT * 
    FROM (SELECT TRUNC(ls.started, 'HH') AS dt,
                 ls.depot
            FROM LOGSTATS ls
        GROUP BY TRUNC(ls.started, 'HH'), ls.depot)
   PIVOT (
     COUNT(*) FOR depot
   )
ORDER BY 1