how to rearrange duplicate sql data into additional column

Question

I have data like this:

|   ID    |    phoneno    
|   01    |    0110111    
|   01    |    0110112    
|   02    |    0220222    
|   03    |    0330333    
|   03    |    0330334    

I'd like to arrange them to:

|   ID    |    phoneno1    |   phoneno2  |  phoneno3  
|   01    |    0110111     |  0110112                
|   02    |    0220222     
|   03    |    0330333     |  0330334    

is that possible with a single SQL statement? Thanks

Answer 1

You didn't tag your backend but this would still work with many backends:

with tmp (id, rowNo, PhoneNo) as 
( 
    select id, row_number() over (partition by id order by phoneno), phoneno
    from myPhones
)
select id,
  max(case when rowno = 1 then phoneNo end) phoneNo1,
  max(case when rowno = 2 then phoneNo end) phoneNo2,
  max(case when rowno = 3 then phoneNo end) phoneNo3
from tmp
group by id;

and here is a DbFiddle link for demo.

PS: Depending on your backend your database might have built-in or extension functions for this (ie: table_func in postgreSQL, pivot in MS SQL server ..._). You might search for pivot or crosstab.

Answer 2

Alternatively , you can show them all in one column as csv:

 select id,string_agg(phoneno, ',') phonenumbers
 from myPhones
 group by id
 order by id;

id	phonenumbers
01	0110111,0110112
02	0220222
03	0330333,0330334

db<>fiddle here