COALESCE in Sub Select? 2

[RichS]

Hello All. I have a Read-Only VB.NET data grid in which I want to show an employee roster with email addresses and phone numbers from an SQL 2000 database. The data needs to look like the following in the data grid:

Employee         Email                      Phone
Clinton, Bill    Bill.Clinton@here.com      Cell:   (502) 111-2222
                 Bill@yahoo.com             Desk:   (502) 111-1234

Bush, George     pres@whitehouse.gov        Cell:   (504) 441-2789
                 dubya@yahoo.com            Desk:   (502) 111-9514
                                            Page:   (445) 789-2442

Public, John Q   jpublic@yahoo.com          Desk:   (502) 111-2587

... Others ....

To do this I am trying to use the sql coalesce statement to prepare a delimited string in which I will use carriage returns in the data grid to bump the multiple email addresses and phone numbers down a line within each cell. The following code returns a comma delimited string in which I can replace the commas with carriage returns.

DECLARE @Email varchar(100)

SELECT @Email = COALESCE(@Email + ', ', '') + 
   CAST(EmailAddress AS varchar(50))
   FROM EmployeeEmail
   WHERE EmployeeID = 8

SELECT @Email

This works well and returns "Bill.Clinton@here.com, Bill@yahoo.com" as needed.

The question is, can this be done from within a sub Select? Or is there a better way to get the data in the format that I wanting?

The stored proc that I have so far is:

ALTER    PROCEDURE  SP_Roster_SelectList_test
AS
    DECLARE @EmailAddress varchar(1000)
    
    SELECT
     e.EmployeeID ,
     e.LastName ,
     e.FirstName ,
  
     EmailAddress = 
      (Select @EmailAddress = COALESCE(em.EmailAddress + ', ', '') +  
       CAST(em.EmailAddress AS varchar(50))
       FROM EmployeeEmail em
       WHERE em.EmployeeID = e.EmployeeID
       Select @EmailAddress
      ),
                  
    FROM Employees e

    ORDER BY e.LastName, e.FirstName

This returns an error: "

Line 11: Incorrect syntax near '='.

"
Any ideas or advice would be much appreciated.

Rich

 

[RiazKhanmohamed]

Looks like your sql. try this:

SELECT
e.EmployeeID , e.LastName , e.FirstName ,
COALESCE(em.EmailAddress + ', ', '') + CAST(em.EmailAddress AS varchar(50))

FROM Employees e
Inner Join EmployeeEmail em
ON em.EmployeeID = e.EmployeeID
ORDER BY e.LastName, e.FirstName

Think that's right. hope it helps.

 

[RichS]

FredPerry, many thanks for the response!

That's very close. Using the provided code the recordset is now:

ID      LastName  FirstName     EmailAddress

11	Bush      George        dubya@yahoo.com, dubya@yahoo.com
11	Bush      George        pres@whitehouse.gov, pres@whitehouse.gov
10	Clinton   Bill	        Bill.Clinton@here.com, Bill.Clinton@here.com
10	Clinton	  Bill	        Bill@yahoo.com, Bill@yahoo.com
10	Clinton	  Bill	        Billy@hotmail.com, Billy@hotmail.com

Looking for:

ID      LastName  FirstName     EmailAddress

11	Bush      George        dubya@yahoo.com, pres@whitehouse.gov
10	Clinton   Bill	        Bill.Clinton@here.com, Bill@yahoo.com, Billy@hotmail.com

I just know this is getting very close.

 

[RiazKhanmohamed]

ooo so close.

i would suggest 'group by e.id, e.lastname, e.firstname'

but that wouldn't help your e-mail addresses.... have to think about this one,sorry

 

[JamesLean]

The only way you'll do this with a single select is to make a user-defined function that takes an employeeid as input and returns the list of emails for that person. Then refer to it like:

SELECT id, lastname, firstname, dbo.GetEmails(id)
FROM employees
ORDER BY lastnane, firstname

You can't convert your initial code which populates the variable into a subquery like you're trying to do - it just won't work!

--James

 

[RichS]

Great! Thanks for the help guys.

In case anyone else is following this thread:

The Stored Procedure:

ALTER    proc SP_Roster_Select_UsingUDF
/**********************************
  Returns Emails in a string.
**********************************/
as

SELECT e.EmployeeID
      ,EmployeeName = e.LastName + ', ' + e.FirstName
      ,EmailList = dbo.GetEmails(e.EmployeeID)
FROM Employees e

ORDER BY e.EmployeeName

And the UDF:

ALTER  FUNCTION dbo.GetEmails(
    @EmployeeID int
    )
Returns varchar(1000)
AS
BEGIN
  DECLARE @Email varchar(1000)

  SELECT @Email = COALESCE(@Email + ', ', '') + 
   CAST(EmailAddress AS varchar(50))
  FROM EmployeeEmail
  WHERE EmployeeID = @EmployeeID

  Return @Email
END