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 |
|
lott42_gmail
Messages: 146 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] by Moderator 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 |
|
Michel Cadot
Messages: 68693 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 #662255 is a reply to message #662254] |
Fri, 21 April 2017 11:19 |
|
Michel Cadot
Messages: 68693 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
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:45:15 CDT 2024
|