Merge records in one record with a SQL query

Question

I want to merge record with same user id into one record and separate them with ,

for example i have 2 tables like this:

tblUsers:

Id      Name
----------------
1       name1
2       name2
3       name3

tblPhone:

Id      Phone
-----------------
1       p11
1       p12
2       p21
2       p22
2       p23

I need sql query that return these results:

Id      Name      Phone
------------------------------
1       name1     p11,p12
2       name2     p21,p22,p23
3       name3     null

any suggestion?

[link]( http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Nikhil Kudtarkar, Dec 09 '13 at 11:35
Actually my mind is completely empty for such a stupid results :D — HaMeD, Dec 09 '13 at 11:36

score 1 · Answer 1 · answered Dec 09 '13 at 11:36

1

This query will do the job

select u.Id, u.Name,
stuff((select ','+p.Phone 
       from tblPhone p 
       where p.Id = u.Id 
       for XML path('')),1,1,'')
from tblUsers u

SQL Fiddle demo

answered Dec 09 '13 at 11:36

Szymon

42,577
16
96
114

score 0 · Accepted Answer · answered Dec 09 '13 at 11:35

0

Try this:

SELECT
   id,Name,
   STUFF((SELECT ','+Phone FROM tblPhone 
           WHERE id=tblusers.Id FOR XML PATH('')),1,1,'')
FROM 
   tblUsers

SQLFiddle demo

answered Dec 09 '13 at 11:35

valex

23,966
7
43
60

score 0 · Answer 3 · answered Dec 09 '13 at 11:38

First of all: Naming conventions of your tblPhone table should be as follows:

ID | UserId | Phone

Where UserId is your link to the users and ID is the tblPhone own Primary key. (preferable a identity seed)

with that approach you can create a for loop for handling the values

DECLARE @table table(userid int, Phones(varchar(max))
DECLARE @min int = 1, @max int = select count(1) from tblPhone

DECLARE @userid int, @Phone varchar(50) 

for(@min <=@max)
BEGIN
  select @userid = UserId, @Phone = Phone from tblPhone where ID = @min
  if(@userid != null)
  BEGIN
    IF NOT EXIST(Select * from @table where userid = @userid)
    BEGIN
      --Create a line
      INSERT INTO @table
      VALUES(@userid, @Phone)
    END
    ELSE
    BEGIN
      --Update the line
      UPDATE @table
      Set Phone = Phone + ', ' + @Phone
      WHERE userid = @userid
    END
  END

SET @Min = @Min + 1
END

Select * from @table

score 0 · Answer 4 · answered Dec 09 '13 at 11:48

0

SELECT Id,Name, Phone= 
STUFF((SELECT ', ' + Phone
       FROM tblPhone 
       WHERE tblUsers.id = tblPhone.id
      FOR XML PATH('')), 1, 2, '')
 FROM tblUsers
 GROUP BY id,Name

answered Dec 09 '13 at 11:48

YOusaFZai

698
5
21

Merge records in one record with a SQL query

4 Answers4