Home » SQL & PL/SQL » SQL & PL/SQL » create dummy rows (Oracle 11.2.0.3)
| create dummy rows [message #649475] |
Mon, 28 March 2016 04:54  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Hi,
CREATE TABLE t_months(NAME VARCHAR2(20), flag VARCHAR2(20), jan VARCHAR2(20), feb VARCHAR2(20), mar VARCHAR2(20))
/
BEGIN
INSERT INTO t_months
VALUES ('SP001', 'Y', 1, 2, 3);
INSERT INTO t_months
VALUES ('SP001', 'N', 1, 0, 0);
INSERT INTO t_months
VALUES ('SP002', 'Y', 1, 2, 3);
INSERT INTO t_months
VALUES ('SP002', 'N', 0, 1, 0);
INSERT INTO t_months
VALUES ('SP003', 'Y', 1, 2, 3);
INSERT INTO t_months
VALUES ('SP004', 'Y', 2, 3, 5);
COMMIT;
END;
/
select * from t_months
/
I would like to display name, flag, jan, feb and mar columns from t_months based on the below logic.
1. Rows with distinct NAME and flag as 'Y' are displayed as it is.
2. Also, for each distinct NAME will check if there is a row with flag as N, if exists will display it, if does not exists,
would like to display a dummy for the NAME with flag as N and jan, feb, and mar values as '0'.
Required output:
NAME FLAG JAN FEB MAR
SP001 Y 1 2 3
SP001 N 1 0 0
SP002 Y 1 2 3
SP002 N 0 1 0
SP003 Y 1 2 3
SP003 N 0 0 0
SP004 Y 2 3 5
SP004 N 0 0 0
I wrote the following query, could you please show any other better ways of doing it.
Thank you in advance.
SELECT t2.NAME, t2.flag, t2.jan, t2.feb, t2.mar
FROM (SELECT t1.NAME, t1.flag, t1.jan, t1.feb, t1.mar,
ROW_NUMBER () OVER (PARTITION BY NAME, flag ORDER BY pick)
val
FROM (SELECT NAME, flag, jan, feb, mar, 1 pick
FROM t_months
UNION ALL
SELECT a.name1, b.flag, b.jan, b.feb, b.mar, 2 pick
FROM (SELECT DISTINCT NAME name1
FROM t_months) a,
(SELECT 'N' flag, '0' jan, '0' feb, '0' mar
FROM DUAL) b) t1) t2
WHERE val = 1
ORDER BY 1, 2 DESC
Regards,
Pointers
|
|
|
|
| Re: create dummy rows [message #649479 is a reply to message #649475] |
Mon, 28 March 2016 06:07  |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please align the columns in result.
SQL> with lines as (select level line from dual connect by level <= 2)
2 select name, nvl(flag, decode(line, 1,'Y', 2,'N')) flag,
3 nvl(jan,0) jan, nvl(feb,0) feb, nvl(mar,0) mar
4 from t_months partition by (name) right outer join lines
5 on (flag='Y' and line=1) or (flag='N' and line=2)
6 order by name, line
7 /
NAME FLAG JAN FEB MAR
----- ----- ----- ----- -----
SP001 Y 1 2 3
SP001 N 1 0 0
SP002 Y 1 2 3
SP002 N 0 1 0
SP003 Y 1 2 3
SP003 N 0 0 0
SP004 Y 2 3 5
SP004 N 0 0 0
[Updated on: Mon, 28 March 2016 06:08] Report message to a moderator |
|
|
|
|
|
| Re: create dummy rows [message #649484 is a reply to message #649483] |
Mon, 28 March 2016 09:44 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Grouping sets solution:
with t as (
select t_months.*,
grouping(flag) grp,
min(flag) over(partition by name) min_flag,
max(flag) over(partition by name) max_flag
from t_months
group by grouping sets((name),(name,flag,jan,feb,mar,rowid))
)
select name,
case
when grp = 1 and min_flag = 'Y' then 'N'
when grp = 1 and min_flag = 'N' then 'Y'
else flag
end flag,
nvl(jan,0) jan,
nvl(feb,0) feb,
nvl(mar,0) mar
from t
where grp = 0
or min_flag = max_flag
order by name,
flag desc
/
NAME FLAG JAN FEB MAR
----- ---- --- --- ---
SP001 Y 1 2 3
SP001 N 1 0 0
SP002 Y 1 2 3
SP002 N 0 1 0
SP003 Y 1 2 3
SP003 N 0 0 0
SP004 Y 2 3 5
SP004 N 0 0 0
8 rows selected.
SQL>
SY.
[Updated on: Mon, 28 March 2016 09:58] Report message to a moderator |
|
|
|
| Re: create dummy rows [message #649485 is a reply to message #649483] |
Mon, 28 March 2016 09:49 |
|
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Could be simplified to:
SQL> select name, f.flag, nvl(jan,0) jan, nvl(feb,0) feb, nvl(mar,0) mar
2 from t_months m partition by (name)
3 right outer join
4 ( select 'Y' flag from dual union all select 'N' from dual) f
5 on m.flag = f.flag
6 order by m.name, f.flag desc
7 /
NAME F JAN FEB MAR
----- - ----- ----- -----
SP001 Y 1 2 3
SP001 N 1 0 0
SP002 Y 1 2 3
SP002 N 0 1 0
SP003 Y 1 2 3
SP003 N 0 0 0
SP004 Y 2 3 5
SP004 N 0 0 0
8 rows selected.
|
|
|
|
| Re: create dummy rows [message #649486 is a reply to message #649483] |
Mon, 28 March 2016 09:57 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Question is if name, flag combination is unique. Michel's solution assumes it is. My solution assumes there can be multiple rows with same name and flag. If name, flag combination is indeed unique model solution might be simpler:
select *
from t_months
model
partition by(name)
dimension by(flag)
measures(jan,feb,mar)
rules(
jan[for flag in ('Y','N')] = nvl(jan[cv()],0),
feb[for flag in ('Y','N')] = nvl(feb[cv()],0),
mar[for flag in ('Y','N')] = nvl(mar[cv()],0)
)
/
NAME FLAG JAN FEB MAR
----- ---- --- --- ---
SP002 Y 1 2 3
SP002 N 0 1 0
SP001 Y 1 2 3
SP001 N 1 0 0
SP004 Y 2 3 5
SP004 N 0 0 0
SP003 Y 1 2 3
SP003 N 0 0 0
8 rows selected.
SQL>
SY.
|
|
|
|
| Re: create dummy rows [message #651109 is a reply to message #649486] |
Wed, 11 May 2016 08:33 |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Hi,
Thank you very much Micheal and Solomon for teaching different ways of doing it.
It worked very well.
However, there are slight enhancements to the question.
i.e. For each NAME there would be at most two records with one flag as Y and another flag as N.
e.g. NAME FLAG JAN FEB MAR
---- ---- --- --- ---
SP001 Y 1 2 3
SP001 N 1 0 0
There are chances that for each NAME there could be only one record either with flag 'Y' or 'N'.
NAME FLAG JAN FEB MAR
---- ---- --- --- ---
e.g. SP003 Y 1 2 3 <---NAME is SP003
SP004 Y 2 3 5 <---NAME is SP004, FLAG could be 'N' also
Now, I would like to generate all the missing rows along with one extra row for each NAME which represent sum of values in record 'N' and record 'Y'
I/P:
NAME FLAG JAN FEB MAR
---- ---- --- --- ---
SP001 Y 1 2 3
SP001 N 1 0 0
SP002 Y 1 2 3
SP002 N 0 1 0
SP003 Y 1 2 3
SP004 Y 2 3 5
SP005 N 6 7 8
SP005 Y 1 1 1
O/P:
NAME FLAG JAN1 FEB1 MAR1
---- ---- ---- ---- ----
SP001 N 1 0 0
SP001 Y 1 2 3
SP001 sum 2 2 3
SP002 N 0 1 0
SP002 Y 1 2 3
SP002 sum 1 3 3
SP003 N 0 0 0
SP003 Y 1 2 3
SP003 sum 1 2 3
SP004 N 0 0 0
SP004 Y 2 3 5
SP004 sum 2 3 5
SP005 N 6 7 8
SP005 Y 1 1 1
SP005 sum 7 8 9
With the above teachings of GROUPING SETS, I have written the following query which works as expected.
INSERT STATEMENTS:
Insert into T_MONTHS (NAME, FLAG, JAN, FEB, MAR) Values ('SP001', 'Y', '1', '2', '3');
Insert into T_MONTHS (NAME, FLAG, JAN, FEB, MAR) Values ('SP001', 'N', '1', '0', '0');
Insert into T_MONTHS (NAME, FLAG, JAN, FEB, MAR) Values ('SP002', 'Y', '1', '2', '3');
Insert into T_MONTHS (NAME, FLAG, JAN, FEB, MAR) Values ('SP002', 'N', '0', '1', '0');
Insert into T_MONTHS (NAME, FLAG, JAN, FEB, MAR) Values ('SP003', 'Y', '1', '2', '3');
Insert into T_MONTHS (NAME, FLAG, JAN, FEB, MAR) Values ('SP004', 'Y', '2', '3', '5');
Insert into T_MONTHS (NAME, FLAG, JAN, FEB, MAR) Values ('SP005', 'N', '6', '7', '8');
Insert into T_MONTHS (NAME, FLAG, JAN, FEB, MAR) Values ('SP005', 'Y', '1', '1', '1');
WITH t AS
(SELECT NAME,
CASE
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 15
AND GROUP_ID () = 0
AND MIN (flag) = MAX (flag)
AND MIN (flag) = 'Y'
THEN 'N'
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 15
AND GROUP_ID () = 0
AND MIN (flag) = MAX (flag)
AND MIN (flag) = 'N'
THEN 'Y'
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 15
AND GROUP_ID () = 1
THEN 'sum'
ELSE flag
END flag,
CASE
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 0
THEN TO_NUMBER (jan)
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 15
AND GROUP_ID () = 1
THEN SUM (jan)
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 15
AND GROUP_ID () = 0
THEN 0
END jan1,
CASE
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 0
THEN TO_NUMBER (feb)
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 15
AND GROUP_ID () = 1
THEN SUM (feb)
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 15
AND GROUP_ID () = 0
THEN 0
END feb1,
CASE
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 0
THEN TO_NUMBER (mar)
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 15
AND GROUP_ID () = 1
THEN SUM (mar)
WHEN GROUPING_ID (NAME, flag, jan, feb, mar) = 15
AND GROUP_ID () = 0
THEN 0
END mar1
FROM t_months
GROUP BY GROUPING SETS (NAME, NAME, (NAME, flag, jan, feb, mar)))
SELECT *
FROM t
WHERE flag IS NOT NULL
ORDER BY 1, 2
I would like to know other ways of doing it.
Thank you very much in advance.
Regards,
Pointers
|
|
|
|
| Re: create dummy rows [message #651121 is a reply to message #651109] |
Wed, 11 May 2016 14:18 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> SELECT * FROM t_months ORDER BY name, flag
2 /
NAME FLAG JAN FEB MAR
----- ---- --- --- ---
SP001 N 1 0 0
SP001 Y 1 2 3
SP002 N 0 1 0
SP002 Y 1 2 3
SP003 Y 1 2 3
SP004 Y 2 3 5
SP005 N 6 7 8
SP005 Y 1 1 1
8 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT name, 'N' flag,
2 NVL (SUM (DECODE (flag, 'N', jan)), 0) jan,
3 NVL (SUM (DECODE (flag, 'N', feb)), 0) feb,
4 NVL (SUM (DECODE (flag, 'N', mar)), 0) mar
5 FROM t_months
6 GROUP BY name
7 UNION ALL
8 SELECT name, 'Y' flag,
9 NVL (SUM (DECODE (flag, 'Y', jan)), 0) jan,
10 NVL (SUM (DECODE (flag, 'Y', feb)), 0) feb,
11 NVL (SUM (DECODE (flag, 'Y', mar)), 0) mar
12 FROM t_months
13 GROUP BY name
14 UNION ALL
15 SELECT name, 'sum' flag,
16 SUM (jan) jan,
17 SUM (feb) feb,
18 SUM (mar) mar
19 FROM t_months
20 GROUP BY name
21 ORDER BY name, flag
22 /
NAME FLA JAN FEB MAR
----- --- ---------- ---------- ----------
SP001 N 1 0 0
SP001 Y 1 2 3
SP001 sum 2 2 3
SP002 N 0 1 0
SP002 Y 1 2 3
SP002 sum 1 3 3
SP003 N 0 0 0
SP003 Y 1 2 3
SP003 sum 1 2 3
SP004 N 0 0 0
SP004 Y 2 3 5
SP004 sum 2 3 5
SP005 N 6 7 8
SP005 Y 1 1 1
SP005 sum 7 8 9
15 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 06:42:49 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
