Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

help needed in sql statement

hi EE

i have two tables "Employee" and "Phone"
Table Employee

EmployeeID | Name
_____________ |  ______
1                       AAA
2                        BBB

Table Phone

EmployeeID  PhoneNO
1                      123
1                       456
1                       789
2                         555

what i need that when i make sql statement between the two table i need the result to be like this

EmployeeID  PhoneNo
1                    123 , 456 ,789
2                      555

i dont want to get all the rows as the normal case

thanks
0
Kamal Khaleefa
Asked:
Kamal Khaleefa
3 Solutions
 
ienaxxxCommented:
if MySQL, you can use the GROUP_CONCAT function
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
PortletPaulCommented:
Here's one one to use 'for xml path'; using cross apply
SELECT
*
FROM employee
CROSS APPLY(
            SELECT STUFF(
                          (
                           SELECT ','+ cast(phone.PhoneNO AS varchar)
                           FROM    Phone
                           WHERE   employee.EmployeeID = phone.EmployeeID
                           FOR XML PATH('')
                           )
                         ,1,1,'')
            ) AS ca1 (phones)

Open in new window

it produces this result:
| EMPLOYEEID | NAME |      PHONES |
|------------|------|-------------|
|          1 |  AAA | 123,456,789 |
|          2 |  BBB |         555 |

Open in new window

0
 
Kamal KhaleefaInformation Security SpecialistAuthor Commented:
thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now