How to mount a Select to join columns

Question

I’m having a doubt I don’t even know if it’s possible:

I would like to perform a select that joins two columns.

Otherwise it would be:
Person table with the cod_person fields - name - rg - razao_social - cnpj
Table Client with cod_client field - cod_person
The select concept would be: Fetch all clients by displaying cod_client - name/razao_social - rg/cnpj

Example of recorded data: Table Person:

cod_pessoa  | Nome    | Rg            | Razao_Social       | Cnpj
1           | Luis    | 10.100.100-1  |                    |
2           | Carlos  | 20.200.200-2  |                    |
3           |         |               |Lanchonete do Paulo | 30.300.300/3000-30

Table Client

cod_cliente  | cod_pessoa
1            | 1
2            | 2
3            | 3

I would like the result to be as follows:

cod_cliente  | Nome/Razao_social     |  Cpf/Cnpj
1            | Luis                  | 10.100.100-1
2            | Carlos                | 20.200.200-2
3            | Lanchonete do Paulo   | 30.300.300/3000-30

I would like to get this result because I am doing a project in C# and in one of the forms I perform a survey of customers the result feeds a datagrid. And the visual result of what I’m wanting would be more pleasurable than a result with blank gaps.

I’ve tried it in many ways, the one that came the closest was this:

select cli.cod_cliente, pes.nome, pes.cpf from tab_Cliente cli
inner join tab_Pessoa pes on cli.cod_pessoa = pes.cod_pessoa
where pes.nome like '%%' --(condição)
union
select cli.cod_cliente, pes.razao_social, pes.cnpj from tab_Cliente cli
inner join tab_Pessoa pes on cli.cod_pessoa = pes.cod_pessoa
where pes.razao_social like '%%' --(condição)

Note: the condition will be equal in both select’s

It joins the columns but it presents some flaws such as: it brings blank gaps when it receives empty in condition or brings nothing when in condition it receives 'equal' to the database data.

I am grateful for any suggestion.

--Update-- Reply - based on the answer of dil_oliveira -Thank you very much

**Database Used Sql Server

select 
  cli.cod_cliente, 
  (case when LEN(lTRIM(pes.nome)) > 0 then 
     Nome   
  else
     Razao_Social
  end) as 'Nome/Razao_social',
  (case when LEN(lTRIM(pes.rg)) > 0 then 
     Rg    
  else
     Cnpj
  end) as 'Cpf/Cnpj'
from tab_Pessoa pes
inner join tab_Cliente cli 
on pes.cod_pessoa = cli.cod_pessoa
where pes.nome like '%%' or pes.razao_social like '%%' order by 'Nome/Razao_social'

Answer 1

You have not specified the database and this code works in Mysql, for other Sgdbs search correspondents for the LENGTH and TRIM functions;

select 
  cod_cliente, 
  (case when LENGTH(TRIM(Nome)) > 0 then 
     Nome   
  else
     Razao_Social
  end) as 'Nome/Razao_social',
  (case when LENGTH(TRIM(Rg)) > 0 then 
     Rg    
  else
     Cnpj
  end) as 'Cpf/Cnpj'
from Pessoa
inner join Cliente 
on Pessoa.cod_pessoa = Cliente.cod_pessoa

There is a more practical way to solve your problem, for example by creating a column in the Person table to define whether the type of record is person or company.