Oracle: pivot (coalesce) counting a single row?

Question

update: what i called coalesce i would have to call pivot.

I am extracting some daily usage counts from a log table. I can easily get this data one row per day / item, but I would like to combine combine the columns into one row.

For example, I have:

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

I want to:

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

Here is my current 10g request.

 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 

update: 11g has a summary operation. The accepted answer shows how to do this in 9i and 10g.

Answer 1

What you are looking for is a turning point - wrapping row data into columns.

Oracle 9i + using WITH / CTE:

---

Using:

 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 

Equivalent Non-WITH / CTE

---

Using:

  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 

Previously, Oracle9i needs CASE statements changed to DECODE , Oracle-specific IF / ELSE logic.

Oracle 11g + using PIVOT

---

Unverified:

  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 

Answer 2

Well, I can at least provide 11 solutions; use pivot:

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html

The only 10 megapixel option that I can think of (I'm decent with SQL, but not an expert) is to populate a table variable and then select individual rows from this table for your final result. Ugly and most likely pretty slow, but can do the job.