February 12, 2010 at 12:07 pm
I would likle to perform the following query:
SELECT EMAIL FROM FRIENDS_LIST
The output should be in a string with commas between each record. If a friend has not included their e-mail address (in other words EMAIL IS NULL then certainly do not place a comma between other foriends email)
The results should look like this based on the following table data:
3 null
The results need to be presented in string format in other words not as records. I would prefer not to dump results into a temp table
Results would look like this:
Bob@hotmail.com,Frank@msn.com,George@yahoo.com
The results are going into an ASP page for the internet and if I needed to output this into table format then I could certainly just loop through each record set based on the query. The magic is not including null values and also not putting a comma at the very last record.
February 12, 2010 at 8:56 pm
There are several posts on this site that address this sort of thing. Here are 2 ways.
-- drop table #Friends_List
GO
create table #Friends_List (row int identity(1,1), email varchar(100))
GO
insert into #Friends_List select 'Bob@hotmail.com'
GO
insert into #Friends_List select 'Frank@msn.com'
GO
insert into #Friends_List select null
GO
insert into #Friends_List select 'George@yahoo.com'
GO
-- select * from #Friends_List
-- This answer assumes
-- 1) that every row has a row number and
-- 2) it's ok to use variables
declare @email varchar(1000), @MaxRow int
select @MaxRow = max(row) from #Friends_List where email is not null
set @email = ''
select @email = @email +
case
when a.email is not null and a.row < @MaxRow then a.email + ','
when a.email is not null and a.row = @MaxRow then a.email
else ''
end
-- select *
from #Friends_List a
where a.email is not null
select @email as email
Without the 'no comma at the end' restriction this gets the same output and you can eaily remove the trailing comma.
declare @email varchar(1000)
set @email = ''
select @email = @email + email + ','
from #Friends_List
where email is not null
select @email as email
February 13, 2010 at 6:53 am
Thanks, but what is your recomendation for removing the last comma after the last record returned. This what I do not know what to do. Thanks
February 26, 2010 at 9:31 pm
The first example does not leave a trailing comma.
For the second example, this should do:
declare @email varchar(1000)
set @email = ''
select @email = @email + email + ','
from #Friends_List
where email is not null
if right(@email, 1) = ','
begin
set @email = left(@email, len(@email)-1)
end
select @email as email
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply