List users' phones from a Join without duplicating lines

Asked

Viewed 70 times

2

Good night,

Guys, I have the following problem:

I need to make a Join in two tables, USER and CONTACT. Each user has more than one contact (Phone), so when I list the data he returns me lines "duplicated" with different phones because, EX:

ID  | Nome      | Telefone
1   | Felipe    | 968181444
1   | Felipe    | 37056781
2   | Claudio   | 37059999
2   | Claudio   | 968899999

It is possible for me to create a dynamic phone column to list the data in this way below?

ID  | Nome      | Telefone 1    | Telefone 2
1   | Felipe    | 968181444     | 37056781
2   | Claudio   | 37059999      | 968899999

I googled a lot, I found something about PIVOT but I could not develop something similar to what I need.

Follow the query:

SELECT u.ID, u.NOME, c.TELEFONE
FROM USUARIO u INNER JOIN CONTATO c
ON c.USUARIO_ID = u.ID
ORDER BY u.NOME

Any solution to this?

sql query oracle

  • Ask your question the code you have already tested to try to come up with a solution.

  • This will not be easy to do, since the number of phones is dynamic. The only way that might solve is with dynamic SQL.

  • Hi, Philippe, I was checking in here and there’s a article by Stackoverflow-EN that answers your question.

  • Try subquery, something like: SELECT u.usuario_id AS Usu , u.nome , c.telefone AS phone1, (SELECT telefone from telefone t Where t.usuario_id = u.usuario_id AND t.telefone != phone1 LIMIT 1) AS phone2 FROM usuario u INNER JOIN telefone c ON c.USUARIO_ID = u.USUARIO_ID GROUP BY u.usuario_id ORDER BY u.name

  • Search by pivot operator.

  • I got it through the graciomar tip. I put a subquerie for phone 2 to create a new column.

  • https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030 , listagg creates a single column but concatenates the values , another solution and , having something that identifies making a case with max e group by

Show 2 more comments

1 answer

0


I got it from the grace tip. I placed a subquerie for phone 2 to create a column, with a change, I had to insert a column in the table CONTACT to know if the phone is 1º or 2º. It’s not the most elegant way but it solves.

SELECT DISTINCT u.ID as ID, u.NOME as NOME, (select 
t1.TELEFONE FROM CONTATO t1 WHERE t1.USUARIO_ID = c.ID AND t1.TELEFONE_POS = 
'1')  as TELEFONE, (select t2.TELEFONE FROM CONTATO t2 WHERE t2.USUARIO_ID = 
c.ID AND t2.TELEFONE_POS = '2') AS TELEFONE2
FROM USUARIO u INNER JOIN CONTATO c
ON c.USUARIO_ID = u.ID
ORDER BY u.NOME

Browser other questions tagged sql query oracle

You are not signed in. Login or sign up in order to post.