Solved

help needed in sql statement

Posted on 2013-10-23
5
305 Views
Last Modified: 2013-10-23
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
Comment
Question by:Kamal Khaleefa
5 Comments
 
LVL 10

Expert Comment

by:ienaxxx
ID: 39593569
if MySQL, you can use the GROUP_CONCAT function
0
 
LVL 10

Accepted Solution

by:
ienaxxx earned 300 total points
ID: 39593570
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 100 total points
ID: 39593595
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 39593635
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
 
LVL 16

Author Closing Comment

by:Kamal Khaleefa
ID: 39593654
thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question