CODEWITHSUNDEEP
sqlpostgresql
Christoph D
Christoph D

Reputation: 23

How to get these both results in one Row?

I have an query which collects the results of my table.

Here is the query:

SELECT id, number FROM from list
union select id, number from list

both fields are integer and the id is doubled but the number is unique.

this is the result

id     number
19564  234287
19564  234288

What do I have to do to that it looks like this: 

id     number
19564  234287;234288

Upvotes: 1

Views: 53

Answers (2)

Ricardo J. Chamorro
Ricardo J. Chamorro

Reputation: 126

I think you need a crosstab query

/* TO CREATE CROSSTAB EXTENSION*/
CREATE EXTENSION IF NOT EXISTS tablefunc;

/* CROSSTAB QUERY */
SELECT * FROM crosstab (  
  'SELECT
    id,
    number   
  FROM list 
  ORDER BY id ASC, number ASC;')
 AS ct(
   id INT,
   firstnumber NUMERIC,
   secondnumber NUMERIC )
 ;

RESULT:

|___id___|_firstnumber_|_secondnumber_|
| 19564  |   234287    |    234288    |

If that works to you and you want both amounts in one single column, you could try concatenating them.

Hope it helps.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Are you looking for string_agg()?

select id, string_agg(distinct number, ';')
from list
group by id;

Upvotes: 2

Related Questions