Excellent
MOHAMMED TAJMUL, July 12, 2017 - 7:09 am UTC
Appreciate your quick help.
A reader, July 14, 2017 - 7:55 am UTC
Create table tt ( col1 varchar2(10),col2 varchar2(10)
Insert into tt values('John','abcd')
select *from tt
----------------
col1 col2
---- ---------
john abcd
i want oupput like this
john a
john b
john c
john d
I tried with below code but it's not getting...
unpivot
--------
select *from(select col1, col2 from tt)
unpivot
(col1 for col2 in('a','b','c','d'))
July 14, 2017 - 8:59 am UTC
How does this relate to the question asked?
Generalized, especially if not IGNORE NULLS unavailable
Duke Ganote, July 14, 2017 - 4:33 pm UTC
I've had similar situations; the most general approach I know is:
with rws as (
select 0 x, null y from dual union all
select 1 x, 'Q' y from dual union all
select 2 x, null y from dual union all
select 3 x, 'A' y from dual union all
select 4 x, null y from dual union all
select 5 x, 'X' y from dual union all
select 6 x, null y from dual
),
smoothing AS (
select x, y
, COALESCE
( MIN(CASE WHEN y IS NOT NULL
THEN x
END)OVER
(ORDER BY x DESC)
, MAX(CASE WHEN y IS NOT NULL
THEN x
END)OVER
(ORDER BY x ASC)
) AS Sullenberger#
from rws
)
select x, y
, max(y)OVER
(partition by Sullenberger#)
as gapfree_y
from smoothing
order by 1;
X Y G
----- - -
0 Q
1 Q Q
2 A
3 A A
4 X
5 X X
6 X
July 16, 2017 - 7:21 am UTC
Nice stuff