How to add condition while pivoting in oracle?

Question

I have got a table like this one.

ID            Month            Event            Count
-----------------------------------------------------  
1              Jan               A                5
1              Jan               B                4
1              Feb               B                3
2              Feb               A               12
2              March             B                8
2              April             C                5

Now what I want is to convert unique event types as separate columns and put respective counts for each of them. The result should look like this-

ID            Month            A            B            C
----------------------------------------------------------  
1              Jan              5           4            0
1              Feb              0           3            0
2              Feb             12           0            0
2              March            0           8            0
2              April            0           0            5

I'm using oracle. What I can think off is something like this-

    select  
        ID,
        Month,
        nvl(a,0) a,
        nvl(b,0) b,
        nvl(c,0) c,
        nvl(d,0) d,
        nvl(e,0) e,
        nvl(f,0) f
    from
        t 
    PIVOT
    (
        sum(Count) for (Event) in (
            1 as a,
            2 as b,
            3 as c,
            4 as d,
            5 as e,
            6 as f
        ) 
    )

Obviously, this code won't work. I need to find something which considers Month column while pivoting. Is there a good way to do this?

Answer 1

Please check this.

CREATE TABLE EVENT(ID NUMBER, MONTH VARCHAR2(10), EVENT CHAR(2));

INSERT INTO EVENT VALUES (1,'JAN','A');
INSERT INTO EVENT VALUES (2,'FEB','B')
INSERT INTO EVENT VALUES (3,'MAR','C');
INSERT INTO EVENT VALUES (1,'JAN','A');

SELECT * FROM EVENT
PIVOT 
(
  COUNT(*)
  FOR EVENT IN ('A','B','C')
);

Thanks, Jalal

Answer 2

With a subquery. See comments within code.

SQL> WITH
  2     -- sample data
  3     test (id,
  4           month,
  5           event,
  6           ccount)
  7     AS
  8        (SELECT 1, 'jan', 'a',  5 FROM DUAL       UNION ALL
  9         SELECT 1, 'jan', 'b',  4 FROM DUAL       UNION ALL
 10         SELECT 1, 'feb', 'b',  3 FROM DUAL       UNION ALL
 11         SELECT 2, 'feb', 'a', 12 FROM DUAL       UNION ALL
 12         SELECT 2, 'mar', 'b',  8 FROM DUAL       UNION ALL
 13         SELECT 2, 'apr', 'c',  5 FROM DUAL)
 14  -- use PIVOT query as a subquery, and apply NVL to columns fetched from it
 15  SELECT id,
 16         month,
 17         NVL (a, 0) a,
 18         NVL (b, 0) b,
 19         NVL (c, 0) c
 20    FROM (SELECT *
 21            FROM test
 22                 PIVOT (SUM (ccount)
 23                       FOR event
 24                       IN ('a' AS a, 'b' AS b, 'c' AS c)))
 25  ORDER BY id, TO_CHAR(TO_DATE(month, 'mon'), 'mm');

        ID MON          A          B          C
---------- --- ---------- ---------- ----------
         1 jan          5          4          0
         1 feb          0          3          0
         2 feb         12          0          0
         2 mar          0          8          0
         2 apr          0          0          5

SQL>