Oracle query tabular form

Question

Oracle query tabular form

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

start in database and I have a question, suppose I have a table called WORLD that has the following values:

I want to get the following result:

Could you please support me as to how this consultation would be carried out, thank you very much, I thank you

The following query:

select case when Pais is null then CONTINENTE else '    ' || Pais end as 
   Consulta
   from
  (
  select distinct CONTINENTE, null as Pais from PAIS
    union
  select CONTINENTE, Pais from PAIS
  ) D

Return me as follows:

And would you look for a continent to appear only once, any suggestions?

When performing the Query indicated by Miguel:

select case when Pais is null then CONTINENTE else '    ' || Pais end as Consulta from ( select distinct CONTINENTE, null as Pais from PAIS    union select CONTINENTE, Pais from PAIS ) D;

I get this, only that it appears at the end of each series and I do not understand very well how to make it appear at the beginning

sql oracle

asked by OrioonTV 10.02.2018 в 01:12

source

2 answers

Improve method to return a priority value within a list Drag marker in Android Google Maps

score 2 · Answer 1

Try this:

 select case when Pais is null then Contiene else '    ' || Pais end as Consulta
    from
    (
    select distinct Contiene, null as Pais from ciudades
        union
    select Contiene, Pais from ciudades
    ) D
order by Continente, Pais nulls first

If it is Sql Server instead of Oracle, simply change the || by +

score 0 · Answer 2

try a group by from the continents:

select case when Pais is null then CONTINENTE else '    ' || Pais end as 
 Consulta
 from
(
  select distinct CONTINENTE, null as Pais from PAIS
    union
    select CONTINENTE, Pais from PAIS
) D
GROUP BY CONTINENTE