one user many phones in a temp table

Question

I need to show in a temporary table one user a many phone of that user, but I'm stuck in the select, I need something like this:

user1        phone1   phone2    phone3   phone4  phone5
11816116-5   8555588  77877888  33254477 224474  45777885

this is the code that I'm trying:

select
    phone As phonenum 
Into #Tmp_phonenumber 
From 
    clients_has_phones 
where 
    user_number='11816116-5'

thanks in advance.

is a table with information about clients and has rows like name, addres,etc. but in the temporary table I just need the userid and all phone numbers of that person, thanks in advance. — suely, Jul 13 '12 at 18:09
@suely we need the actual table schema ex. does each row only have 1 phone number? Are there multiple rows where user_number is the same? etc. It would help if you could edit your question to show an example of the table (so we can replicate the table) to help figure out your issue. — Brad, Jul 13 '12 at 18:22
is this what you are trying to achieve? http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Samson, Jul 13 '12 at 18:31
I upload an image @Brad this is: [1]: http://i.stack.imgur.com/4d4ez.png — suely, Jul 13 '12 at 18:51
this is the schema @radashk [1]: http://i.stack.imgur.com/4d4ez.png — suely, Jul 13 '12 at 18:51
@suely is there a max limit of phone numbers a individual can have? — Brad, Jul 13 '12 at 19:47
@Brad no...an user can have all phonenumbers that it wants, thanks in advance. — suely, Jul 13 '12 at 20:48

score 1 · Answer 1 · answered Jul 13 '12 at 20:55

I can not think of a good way of doing the select statement other than by self joining on how ever many phone numbers your user may have.. With that being said you can try this for your select statement:

;With CTE_Main as (
Select
  id
  ,Fono
  ,row_number()
    Over(Partition by ID order by Fono) as RN
From sucursales
), CTE_Users as (
Select
  id as id_num
  from sucursales
  group by id
)
Select
  id_num
  ,a.Fono as Phone_1
  ,b.Fono as Phone_2
  ,c.Fono as Phone_3
  ,d.Fono as Phone_4
  ,e.Fono as Phone_5
From CTE_Users as realz
  Left Join [CTE_Main] as a on a.id = realz.id_num and a.RN = 1
  Left Join [CTE_Main] as b on b.id = realz.id_num and b.RN = 2
  Left Join [CTE_Main] as c on c.id = realz.id_num and c.RN = 3
  Left Join [CTE_Main] as d on d.id = realz.id_num and d.RN = 4
  Left Join [CTE_Main] as e on e.id = realz.id_num and e.RN = 5

I know its kind of lengthy but it will display the results in the way that you want them.. My example only uses 5 rows but it should be pretty self explanatory.

Sql Fiddle: http://sqlfiddle.com/#!3/496f6/1

it probably works..but you need to know how many phones each one has. Self joins are usually used when searching for occurences. — Samson, Jul 13 '12 at 23:38

one user many phones in a temp table

1 Answers1