Home » SQL & PL/SQL » SQL & PL/SQL » Pivot result using "Pivot" keyword (11.2)
Pivot result using "Pivot" keyword [message #662217] Thu, 20 April 2017 18:06 Go to next message
lott42
Messages: 117
Registered: June 2010
Senior Member
I'm trying to pivot a result set to look like this
STATUS	DEPT_10	DEPT_20	DEPT_30	ALL_DEPT
a	1	1	0	2
b	0	2	1	3

Here's my data
WITH data AS
 (SELECT 'a' status, 10 dept
    FROM dual
  UNION ALL
  SELECT 'a' status, 20 dept
    FROM dual
  UNION ALL
  SELECT 'b' status, 20 dept
    FROM dual
  UNION ALL
  SELECT 'b' status, 20 dept
    FROM dual
  UNION ALL
  SELECT 'b' status, 30 dept
    FROM dual)

Doing a pivot w/o using the "Pivot" statement
SELECT d.status,
       COUNT(decode(d.dept, '10', 1)) dept_10,
       COUNT(decode(d.dept, '20', 1)) dept_20,
       COUNT(decode(d.dept, '30', 1)) dept_30,
       COUNT(d.dept) all_dept
  FROM data d
 WHERE 1 = 1
 GROUP BY d.status
 ORDER BY d.status;

but, when I try the "Pivot" version
SELECT t.*
  ,COUNT(*) over(PARTITION BY t.status) all_dept
  FROM (SELECT d.status, d.dept, COUNT(*) cnt
          FROM data d
         GROUP BY d.status, d.dept) pivot(SUM(cnt) FOR dept IN('10' AS
                                                               dept_10,
                                                               '20' AS
                                                               dept_20,
                                                               '30' AS
                                                               dept_30)) t
 ORDER BY t.status;

I get these results:
STATUS	DEPT_10	DEPT_20	DEPT_30	ALL_DEPT
a	1	1		1
b		2	1	1

So,
1. How do I get the "all_dept" totals for status('a' and 'b') like the first example??
I did try using "partition" in different spots but not sure if/where this is even correct...
2. Where would I put the "nvl" on each "dept" to display "0" and not a "null" value

[Updated on: Thu, 20 April 2017 18:15]

Report message to a moderator

Re: Pivot result using "Pivot" keyword [message #662226 is a reply to message #662217] Fri, 21 April 2017 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64967
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> WITH data AS
  2   (SELECT 'a' status, 10 dept
  3      FROM dual
  4    UNION ALL
  5    SELECT 'a' status, 20 dept
  6      FROM dual
  7    UNION ALL
  8    SELECT 'b' status, 20 dept
  9      FROM dual
 10    UNION ALL
 11    SELECT 'b' status, 20 dept
 12      FROM dual
 13    UNION ALL
 14    SELECT 'b' status, 30 dept
 15      FROM dual)
 16  select status, dept_10, dept_20, dept_30, dept_10+dept_20+dept_30 all_dept
 17  from data
 18       pivot ( count(*) for dept in (10 as dept_10, 20 as dept_20,30 as dept_30))
 19  /
S    DEPT_10    DEPT_20    DEPT_30   ALL_DEPT
- ---------- ---------- ---------- ----------
a          1          1          0          2
b          0          2          1          3
Re: Pivot result using "Pivot" keyword [message #662254 is a reply to message #662226] Fri, 21 April 2017 10:01 Go to previous messageGo to next message
lott42
Messages: 117
Registered: June 2010
Senior Member
Thanks, Michel, that was it. I'm not sure why I used a "t" reference but that seems to have thrown off my results(null instead of zero). Also, maybe I was overthinking the "all_dept" column reference. Just add them up like you did.

Thanks, again

[Updated on: Fri, 21 April 2017 10:04]

Report message to a moderator

Re: Pivot result using "Pivot" keyword [message #662255 is a reply to message #662254] Fri, 21 April 2017 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64967
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
but that seems to have thrown off my results(null instead of zero).
No that's not this, SUM (what you used) returns NULL if there are no rows, COUNT(*) never returns NULL.

SQL> select sum(1) from dual where 1=2;
    SUM(1)
----------


1 row selected.

SQL> select count(*) from dual where 1=2;
  COUNT(*)
----------
         0

1 row selected.

[Updated on: Fri, 21 April 2017 14:28]

Report message to a moderator

Re: Pivot result using "Pivot" keyword [message #662256 is a reply to message #662255] Fri, 21 April 2017 12:22 Go to previous message
lott42
Messages: 117
Registered: June 2010
Senior Member
Michel, thanks for the clarification. I see the difference now
Previous Topic: LIKE vs NOT LIKE operator
Next Topic: Taking Sum of Similar Tables
Goto Forum:
  


Current Time: Wed Jun 28 14:20:50 CDT 2017

Total time taken to generate the page: 0.09760 seconds