Split the values that exist in the same column in a table using a separator

Question

I have a table as shown below :

                     TBLContact
-------------------------------------------------
BranchId  DepartmentId  DepartmentName   Phone No
-------------------------------------------------
 BID100    DTID001       Mechanical       123654

 BID100    DTID001       Mechanical       887744

 BID101    DTID002       Automobile       045167

 BID101    DTID002       Automobile       674632

I have to structure a query in a stored procedure in a way such that, on passsing 'BranchId' as a parameter to the query it would return the Department Phone No's of the corresponding 'BranchId' in a single row separated by a comma, although the 'Phone No' exists in two rows for a specific 'BranchId'. For Example; Consider passing the value 'BID100' as a parameter to the query/stored procedure, the query would return me a result as below:

BranchId DepartmentId  DepartmentName   PhoneNo
---------------------------------------------------
 BID100    DT001        Mechanical    123654,887744

PS: I am using SQL Server 2008.

Have a look at [this](http://stackoverflow.com/a/545672/1223446) answer — Terence, Jun 24 '13 at 09:53

score 0 · Answer 1 · answered Jun 24 '13 at 10:16

0

Here an example shown via the XML data() command in MS SQL Server is:

select PhoneNo + ', ' as 'data()' 

from TBLContact where branch_id = BID100

for xml path('')

answered Jun 24 '13 at 10:16

Ankur

126
9

score 0 · Answer 2 · answered Jun 24 '13 at 11:10

Use this script as basis

declare @PhoneNo VARCHAR(8000) 

select @PhoneNo = COALESCE(@PhoneNo+ ', ', '') + PhoneNo
from table
where BranchId = 'BID100'

select top 1  BranchId,  DepartmentId,  DepartmentName, @PhoneNo
from table
where BranchId = 'BID100'

Split the values that exist in the same column in a table using a separator

2 Answers2