Problem solve Get help with specific problems with your technologies, process and projects.

Displaying all of one person's phone numbers in one column

I have a table with multiple records. For example

 id,Name,Phonenumber,Eid 1 Josh 6565656 4 2 Paul 7676767 6 3 Josh 8633545 4 4 Josh 7686849 4
I would like to copy the data into a temptable with all the phone numbers of Josh in one column, like (6565656,7676767,8633545,7686849). I tried with decalring cursors, but did not succeed.

With the introduction on functions you can achieve this without cursors or temporary tables by way of a function. For example if I assume your table structure is:

 CREATE TABLE PhoneRecords ( [id] INT PRIMARY KEY, [Name] varchar(50), PhoneNumber varchar(10), Eid int ) INSERT PhoneRecords VALUES(1,'Josh',6565656,4) INSERT PhoneRecords VALUES(2,'Paul',7676767,6) INSERT PhoneRecords VALUES(3,'Josh',8633545,4) INSERT PhoneRecords VALUES(4,'Josh',7686849,4)
We can produced the desired result with the following function:
 CREATE FUNCTION fn_phonenumbers(@eid int) RETURNS VARCHAR(8000) AS BEGIN DECLARE @phoneno varchar(8000) SELECT @phoneno= IsNull(@phoneno + ',' + PhoneNumber,PhoneNumber) FROM PhoneRecords WHERE [eid]=@eid RETURN @phoneno END
and running the following query:
 SELECT DISTINCT name,dbo.fn_phonenumbers(eid) FROM PhoneRecords
Which produces the result shown below:
 Josh 6565656,8633545,7686849 Paul 7676767
Just be aware that this is usually consider bad query design for a number of reasons, not least of which is that it will break as soon as you have a total length of all phone numbers per EID greater than 8000 characters.


For More Information

This was last published in June 2002

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.