Need help with putting results into a string

[DougP]

I have this code but it only contains the last email.
I want to get everyones email in a string to send them email notifications.
like so
fred@nowhart.com, sue@gert.com, gerry@not.com

I was hoping to do this without looping using a cursor

DECLARE @results VarChar(1000)
SELECT @results = CASE
     WHEN @results IS NULL THEN CONVERT( VarChar(20), [email])
     ELSE ', ' + CONVERT( VarChar(20), [email])
   END
FROM dbo.SOWEmailManagersHistory 

Select @results

DougP

 

[DougP]

Sorry the code I got is junk, I don't want to strip out 20 characters
still need fred@nowhart.com, sue@gert.com, gerry@not.com

DougP

 

[imex]

Try:

SELECT STUFF( (SELECT ', ' + [email]
               FROM dbo.SOWEmailManagersHistory
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
               ,1, 2, '')

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[DougP]

OK so now I have this code that I want to get into one string...
but it errors saying:

	Declare  @Today  Date
	Declare  @Monday  Date
	Declare @MondayCount int
	Declare @TuesdayCount int

	Set @Today = GETDATE()
	Set @Monday =(Select GETDATE() -1)
	
	SELECT STUFF( 
               (SELECT ', ' + [email]
                from SOWEmailManagersHistory where 
				convert(date,DateEntered,101)  = @Today
				Except
				Select Email from SOWEmailManagersHistory where 
				convert(date,DateEntered,101) = @Monday
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
               ,1, 2, '')

DougP

 

[TheBugSlayer]

Or try

	select REPLACE(
	( 
        SELECT 
            REPLACE(Email, CHAR(13), '') AS Email
        FROM 
            dbo.SOWEmailManagersHistory 
        FOR XML PATH ('')
    ), 
	' ', 
	', ')

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[imex]

Try:

SELECT STUFF( (SELECT ', ' + t.[email]
               from SOWEmailManagersHistory as t
               where 
		   convert(date, t.DateEntered, 101)  = @Today and
                   not exists (select 1
			       from SOWEmailManagersHistory as m
                               where 
                                   m.[email] = t.[email] and
                                   convert(date, m.DateEntered, 101) = @Monday)
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
               ,1, 2, '')

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[DaveInIowa]

I think you're original query was close. How about

DECLARE @results VarChar(1000) = NULL
 SELECT @results = CASE WHEN @results IS NULL THEN email ELSE [red]@results[/red] + ', ' + email END
   FROM dbo.SOWEmailManagersHistory

 

[TheBugSlayer]

A small change, to avoid adding a comma for every blank space:

select REPLACE(
	( 
        SELECT 
            REPLACE(Email, CHAR(13), '') + '*' AS Email
        FROM 
            dbo.SOWEmailManagersHistory 
        FOR XML PATH ('')
    ), 
	'*', 
	', ')

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[philhege]

declare @results varchar(max) = ''
select @results = @results + coalesce(email+', ','')
FROM dbo.SOWEmailManagersHistory
--strip last comma
set @results = substring(@results,1,len(@results)-1)
select @results

-----------
With business clients like mine, you'd be better off herding cats.