CODEWITHSUNDEEP
sqlsql-server
Mharveen Biu
Mharveen Biu

Reputation: 325

Need Solution. 2 row into 1 row sql server 2008

This is my 2 normalize table which has Clientid into Telephone table 

(ClientID int Primary key,
FName varchar(25),
LName varchar(25),
HomeAddress varchar(50))

CREATE TABLE Telephone
(TelephoneID tinyint IDENTITY(1,1)Primary key,
TelephoneNo int,
ClientID int foreign key references Client(ClientID))

so the values for my Client table..

ClientID | FName  | LName |  HomeAddress   
    1      marvin    Biu    p.guevarra st. 
    2      harry    sendon     cali st.

and into my Telephone table..

 TelephoneID | TelephoneNo | ClientID  
        1        1234567        1
        2        7654321        1
        3        2222222        2

since it is possible to that a client has a multiple telephone no so i would like to become like this..

ClientID | FName  | LName |  HomeAddress   |   Telephones
    1      marvin    Biu    p.guevarra st.   1234567, 7654321
    2      harry    sendon     cali st.          2222222

i only come up with the code like this

select distinct lname, CAST(telephoneno AS VARCHAR(10)) + ',' + CAST(telephoneno AS VARCHAR(10)) as Telephones
from telephone
left join client
on client.clientid = telephone.clientid

ended up like this..

 LName |   Telephones
 Biu      1234567, 1234567
 Biu      7654321, 7654321
 sendon     2222222

Please anyone help, its ok that the table ended up in a simple form like above shown, i really wanted the 1234567 telephoneno join with 7654321 telephoneno with coma in between in telephones column with one Lname "Biu" column. thats make 1 row. :/

Upvotes: 1

Views: 98

Answers (2)

user804018
user804018

Reputation:

Always qualify your tables in a join, it makes it much clearer to see what's going on:

select distinct t.lname, CAST(t.telephoneno AS VARCHAR(10)) + ',' + CAST(c.telephoneno AS VARCHAR(10)) as Telephones
from telephone t
left join client c
on c.clientid = t.clientid

Upvotes: 0

EricZ
EricZ

Reputation: 6205

SELECT 
  lname,
  Telephones = STUFF((SELECT ','+ CAST(t.telephoneno AS VARCHAR(10)) 
  FROM telephone t
  WHERE t.clientid  = c.clientid 
  For XML PATH('')
  ),1,1,'')
FROM client c

Upvotes: 1

Related Questions