Help with SQL

Hi, i hope i posted int he appropiated forum.
I hope you can help me with an sql sentence i dont know how to do,...

The problem is that i have a database with fields like this:

name, adress, etc.... , phone number

but sometimes, i get them like this:

paul, ... street... , etc... , 776509332
paul, ... street... , etc... , 234536445
paul, ... street... , etc... , 567847345

The client si the same, but he has more than one phone number....
I need to do, somehow, another table that makes this kind of duplicates to dissapear, and joins the phone numbers in an unique field for that person, so it will in the end look like this:

paul, ... street... , etc... , 776509332 234536445 567847345

Can anybody help me?

Thanks

Check back in a hour and I'll have something.|||Thanks!! i really need this code, i will wait patiently
|||

OK; First of all let's start by creating this function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GETPHONENUMBER]
(
@.UserID int
)
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @.STRINGNUMBER NVARCHAR(25)
SET @.STRINGNUMBER = ''
DECLARE @.GETNUMBER NVARCHAR(15)
DECLARE @.NUMBER NVARCHAR(15)
DECLARE USERNUMBERS CURSOR FOR
SELECT
PHONENUMBERS
FROM
TABLENUMBERS
WHERE
USERID = @.USERID

OPEN USERNUMBERS
FETCH NEXT FROM USERNUMBERS
INTO @.NUMBER

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.STRINGNUMBER = @.NUMBER + ',' + CAST(@.STRINGNUMBER AS NVARCHAR(200))
FETCH NEXT FROM USERNUMBERS
INTO @.NUMBER
END

CLOSE USERNUMBERS
DEALLOCATE USERNUMBERS

RETURN @.STRINGNUMBER
END

Now; you can call this function from inside your code like this;

SELECTDISTINCT [NAME],[MYDATABASE].[DBO].[GETPHONENUMBER](USERID)AS'MULTIPLE PHONE NUMBERS'

FROM TABLENUMBERS

WHERE USERID = 1

|||

SELECT t3.nameID, t3.Name,MAX(case t3.seq when 1 then t3.phone end)

+MAX(case t3.seq when 2 then' '+ t3.phone else''end)

+MAX(case t3.seq when 3 then' '+ t3.phone else''end)AS phone

FROM(SELECT nameID,Name, phone,(SELECTCOUNT(*)FROM phones AS t2 WHERE t2.nameID = t1.nameID and t2.phone <= t1.phone)AS seq

FROM phones AS t1

)as t3

GROUPBY t3.nameID, t3.Name

--For SQL Server 2005, you can also use this:

SELECT t3.nameID,coalesce(' '+ t3.[1],'')+coalesce(' '+ t3.[2],'')+coalesce(' '+ t3.[3],'')AS phone

FROM(SELECT nameID, phone, ROW_NUMBER()OVER(PARTITIONBY nameID ORDERBY phone)AS seq FROM phones)as t1

PIVOT(MAX(phone)for seq in([1], [2], [3]))AS t3

|||

If you are using SQL 2005, try this variation also

Createtable #tab( id int, val varchar(50))

insertinto #tab

select 1,'A'unionall

select 1,'B'unionall

select 1,'C'unionall

select 2,'D'unionall

select 2,'E'unionall

select 2,'F'unionall

select 3,'G'unionall

select 3,'H'

GO

with CTE (id,xyz)as

(

select id,xyz=val from #tab

unionall

select a.id,xyz=convert(varchar(24),xyz)+' '+convert(varchar(25),a.val)

from #tab a inner loop join CTE b

on a.id=b.id andpatindex('%'+a.val+'%',xyz)<1

)

selectdistinct id,reverse(max(xyz))from CTE

Groupby id

|||Thank you all for the help.. is for using it in access.. anyway, finally, is sql, and i think i will be able to use it as well.... thank you, really.. to all the people that has helped me
|||

For Access:

SELECT t3.nameID, t3.Name, MAX(IIF( t3.seq= 1, t3.phone+' ', ' ')) +MAX(IIF( t3.seq= 2, t3.phone+' ', ' ')) + MAX(IIF( t3.seq= 3, t3.phone, ' ')) AS phone
FROM [SELECT nameID, Name, phone, (SELECT COUNT(*) FROM phones AS t2 WHERE t2.nameID = t1.nameID and t2.phone <= t1.phone) AS seq
FROM phones AS t1]. AS t3
GROUP BY t3.nameID, t3.Name;

|||The access one will be the good one.. the only thing i need is you to clarify me what is each thing... i mean.. the table structure is just a table with 7 fields, the last one has the phones, what i want to group, and i only have that one table......
Thanks for all the help!!
|||

--For Access

SELECT t3.nameID, t3.Name, t3.Field1, t3.Field2,t3.Field3,t3.Field4, MAX(IIF( t3.seq= 1, t3.phone+' ', ' ')) +MAX(IIF( t3.seq= 2, t3.phone+' ', ' ')) + MAX(IIF( t3.seq= 3, t3.phone, ' ')) AS phone
FROM [SELECT nameID, Name, Field1, Field2,Field3,Field4, phone, (SELECT COUNT(*) FROM phones AS t2 WHERE t2.nameID = t1.nameID and t2.phone <= t1.phone) AS seq
FROM phones AS t1]. AS t3
GROUP BY t3.nameID, t3.Name, t3.Field1, t3.Field2,t3.Field3,t3.Field4;

--Assume nameID, Name, Field1, Field2,Field3,Field4, phone are your 7 fields in your table "phones".

|||Thanks i see it more clearly now...

So, t1, t2, t3 are tables, right? but i only have one....

|||

These three t1, t2, t3 are aliases for your only table. In my sample code, the only table is "phones".

You can replace your table name for "phones" and replace all fields with your table field names. HTH.

|||Ok, thanks, i hope it works!!
I will try it in my job and come back here if i find ny problem...

Thank you!