You Asked
Hi,
Is there a simple way to achieve the following output in SQL?
Wanted to populate the null values with the Country name until there is a new country name.
Thanks
with data as
(
select 'Afghanistan Afghani' as country_currency, NULL as amount, 'Afghanistan' country from dual union all
select 'Kabul' as country_currency, '200' as amount, NULL country from dual union all
select 'Elsewhere' as country_currency, '100' as amount, NULL country from dual union all
select 'Albania (Albania Lek(e))' as country_currency, null as amount, 'Albania' country from dual union all
select 'Tirana' as country_currency, '100' as amount, NULL country from dual union all
select 'Shkodra' as country_currency, '200' as amount, NULL country from dual union all
select 'Vlore' as country_currency, '200' as amount, NULL country from dual union all
select 'Elsewhere' as country_currency, '200' as amount, NULL country from dual union all
select 'Algeria (Algerian Dinar)' as country_currency, null as amount, 'Algeria' country from dual union all
select 'Algiers' as country_currency, '100' as amount, NULL country from dual union all
select 'Adrar' as country_currency, '200' as amount, NULL country from dual union all
select 'Oran' as country_currency, '200' as amount, NULL country from dual union all
select 'Elsewhere' as country_currency, '200' as amount, NULL country from dual
)
select
*
from
data
and Connor said...
Thanks for the test data. We need something which *defines* the order, so I've added a sequence number. After that, its easy with a bit of LAG
SQL> with data as
2 (
3 select 1 x, 'Afghanistan Afghani' as country_currency, NULL as amount, 'Afghanistan' country from dual union all
4 select 2,'Kabul' as country_currency, '200' as amount, NULL country from dual union all
5 select 3,'Elsewhere' as country_currency, '100' as amount, NULL country from dual union all
6 select 4,'Albania (Albania Lek(e))' as country_currency, null as amount, 'Albania' country from dual union all
7 select 5,'Tirana' as country_currency, '100' as amount, NULL country from dual union all
8 select 6,'Shkodra' as country_currency, '200' as amount, NULL country from dual union all
9 select 7,'Vlore' as country_currency, '200' as amount, NULL country from dual union all
10 select 8,'Elsewhere' as country_currency, '200' as amount, NULL country from dual union all
11 select 9,'Algeria (Algerian Dinar)' as country_currency, null as amount, 'Algeria' country from dual union all
12 select 10,'Algiers' as country_currency, '100' as amount, NULL country from dual union all
13 select 11,'Adrar' as country_currency, '200' as amount, NULL country from dual union all
14 select 12,'Oran' as country_currency, '200' as amount, NULL country from dual union all
15 select 13,'Elsewhere' as country_currency, '200' as amount, NULL country from dual
16 )
17 select d.*,
18 nvl(country, lag(country ignore nulls) over ( order by x ) ) as filler
19 from data d
20 order by x;
X COUNTRY_CURRENCY AMO COUNTRY FILLER
---------- ------------------------ --- ----------- -----------
1 Afghanistan Afghani Afghanistan Afghanistan
2 Kabul 200 Afghanistan
3 Elsewhere 100 Afghanistan
4 Albania (Albania Lek(e)) Albania Albania
5 Tirana 100 Albania
6 Shkodra 200 Albania
7 Vlore 200 Albania
8 Elsewhere 200 Albania
9 Algeria (Algerian Dinar) Algeria Algeria
10 Algiers 100 Algeria
11 Adrar 200 Algeria
12 Oran 200 Algeria
13 Elsewhere 200 Algeria
13 rows selected.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment