By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

SQL Query aggregate phone numbers

P: 1
Hi, i have a question, imagine that i have a table with names and phone numbers, like this:

Name | Phone
Jack | 2457349843
Jack | 9347842344
Mary | 4324324423
John | 3123123123
John | 43243244324

I want a query that returns something like this

Name | Phone
Jack | 2457349843, 9347842344
Mary | 4324324423
John | 3123123123, 43243244324

Thanks in advance, i really need this.
Nov 22 '09 #1
Share this Question
Share on Google+
1 Reply


nbiswas
100+
P: 149
Try this(Sql Server 2005+)
-- Table declarations with sample data
Expand|Select|Wrap|Line Numbers
  1. declare @t table(name varchar(50),phone bigint)
  2. insert into @t 
  3.     select 'Jack', 2457349843 union all select 'Jack', 9347842344 union all
  4.     select 'Mary', 4324324423 union all
  5.     select 'John', 3123123123 union all select 'John', 43243244324
--Program Starts
Expand|Select|Wrap|Line Numbers
  1. select name,LEFT(Phone,LEN(Phone) -1) as Phone from (
  2. select t2.name,
  3.         ( 
  4.             select cast(phone as varchar(max)) + ','
  5.             from @t t1
  6.             where t1.name = t2.name
  7.             for XML path('')
  8.         ) as Phone
  9. from @t t2
  10. group by t2.name)X(name,Phone)
Output
----------

name Phone
Expand|Select|Wrap|Line Numbers
  1. Jack    2457349843,9347842344
  2. John    3123123123,43243244324
  3. Mary    4324324423
Nov 23 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.