I have table 1 like this:
id | Country |
---|---|
1 | Germany |
1 | USA |
1 | Japan |
2 | France |
Table 2 like this
id | Color |
---|---|
1 | Green |
2 | Red |
2 | Yellow |
Is it possible to get result like this using SQL statement?:
id | Country | Color |
---|---|---|
1 | Germany | Green |
1 | USA | |
1 | Japan | |
2 | France | Red |
2 | Yellow |
It means that, if id 1 has 3 countries and 1 color --> The result should return only 3 countries and 1 color in any order (and color can be in the same row with any country). Generally, if id 1 has m countries and n color --> The result should return only m countries and n colors ? Thank you very much <3
Note: I'm using Oracle Database
CodePudding user response:
You can number your countries and colors per ID and then use a full outer join on the ID and that number:
with cntr as
(
select id, row_number() over (partition by id order by country) as subid, country
from country
)
, clr as
(
select id, row_number() over (partition by id order by color) as subid, color
from color
)
select id, cntr.country, clr.color
from cntr full outer join clr using (id, subid)
order by id, subid nulls last;
Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=e74ece8cb4571d7998014f8c55bd8d7a
CodePudding user response:
with src as (
select n.id,n.country,l.color,
(select count(id)-1
from countries where id=n.id) total_countries
,(
select count(id)-1
from colors where id=l.id
) total_colors
from countries n
inner join colors l
on n.id = l.id
)
select
id,
country,
lag(color, total_countries) over (partition by id order by color) color
from
src
where total_countries > 0
union all
select
id,
lag(country, total_colors) over (partition by id order by country) country
,color
from
src
where total_colors > 0
order by id, country nulls last, color nulls last