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 4I 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 ENDand running the following query:
SELECT DISTINCT name,dbo.fn_phonenumbers(eid) FROM PhoneRecordsWhich produces the result shown below:
Josh 6565656,8633545,7686849 Paul 7676767Just 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
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.