Simple SQL statement help

[ProPlus]

Get a result of:

eventdate
--------------------------
2006-04-26 00:00:00.000
2006-05-11 00:00:00.000
2006-05-12 00:00:00.000
2006-04-27 00:00:00.000
2006-04-28 00:00:00.000
2006-06-06 00:00:00.000

Using something like

DECLARE @string varchar(255)
select @String = @String + convert(varchar,eventdate) + ','
from phx_eqseventcalendar
where eventtypeid =6 and legid = 4351
select 'Payment Dates['+@String+']'

I want to loop through it so @String = 2006-04-26,2006-05-11,2006-05-12,2006-04-27,2006-04-28,2006-06-06

Any quick and easy ideas that don't include Temp tables and datasets..... Its in an SQL only world so no using JAVA or C#.....

Ta in advance....

[wiggy001]

ProPlus said:

Get a result of:

eventdate
--------------------------
2006-04-26 00:00:00.000
2006-05-11 00:00:00.000
2006-05-12 00:00:00.000
2006-04-27 00:00:00.000
2006-04-28 00:00:00.000
2006-06-06 00:00:00.000

Using something like

DECLARE @string varchar(255)
select @String = @String + convert(varchar,eventdate) + ','
from phx_eqseventcalendar
where eventtypeid =6 and legid = 4351
select 'Payment Dates['+@String+']'

I want to loop through it so @String = 2006-04-26,2006-05-11,2006-05-12,2006-04-27,2006-04-28,2006-06-06

Any quick and easy ideas that don't include Temp tables and datasets..... Its in an SQL only world so no using JAVA or C#.....

Ta in advance....

Google "Cursors" and you'll find something along the lines of:

DECLARE @cursor as CURSOR FOR
SELECT convert(varchar,eventdate) + ','
from phx_eqseventcalendar
where eventtypeid =6 and legid = 4351

OPEN @CURSOR

FETCH NEXT FROM @cursor into @String1
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @String = @String + @String1
FETCH NEXT FROM @cursor into @String1
END

CLOSE @cursor


Hope that helps

[Carfiend]

if its an oracle database then this should do it in some nice plsql:

declare

v_stringdate varchar2;

cursor c1 is
select eventdate
from phx_eqseventcalendar
where eventtypeid =6 and legid = 4351;

begin

for c in c1
loop

v_stringdate := v_stringdate+','+c.eventdate;

end loop;

do something with the string

end;

[ProPlus]

Went for:

CREATE FUNCTION dbo.fEventDates(@LegID int, @EventTypeId int)
RETURNS VarChar(5000) AS
BEGIN
/*
For Example :-
select dbo.fEventDates (5039, 6)
select dbo.fEventDates (5039, 5)
select dbo.fEventDates (5039, 3)
*/
declare @string varchar(5000)
declare @EventDate varchar(25)

SELECT @String = ''

DECLARE INSERT_CURS CURSOR FOR
SELECT convert(varchar,eventdate,06) 'EventDate'
FROM phx_eqseventcalendar
WHERE eventtypeid =@EventTypeId and legid = @LegID
ORDER BY EventDate ASC
FOR READ ONLY



OPEN INSERT_CURS
FETCH NEXT FROM INSERT_CURS INTO
@EventDate

WHILE (@@FETCH_STATUS = 0)
BEGIN
set @String = @String + @EventDate + ', '

-- next row
FETCH NEXT FROM INSERT_CURS INTO
@EventDate
END

CLOSE INSERT_CURS
DEALLOCATE INSERT_CURS

IF @String <> ''
BEGIN
SET @String = Substring(@String, 1, datalength(@String) - 2)
END
return (SELECT @String)

END

in the end, but cheers for the help.