Multiple values int textbox...

Hi,
I need to display the result set in a textbox,
if there are multiple rows i need to show them as comma separated..
Can anyone give me idea of how it can be achieved..
Thanks in advance,I can't think of an easy way.
You need to get the resultset concatanating, not do it in report layout.
You could use a cursor (effectively a loop) in the SQL query - get the
result set and concatanate the rows into a variable and output the
variable as the result set. Cursors are quite slow though.
You could do the same thing in VB code using ADO if that's your thing.
Chris
CCP wrote:
> Hi,
> I need to display the result set in a textbox,
> if there are multiple rows i need to show them as comma separated..
> Can anyone give me idea of how it can be achieved..
> Thanks in advance,sql

Multiple rows into single column

I'm working on a Reports and I need to combine data from multiple
rows (with the same EventDate) into one comma separated string. This is how the data is at the moment:

EmployeeID EventDate
2309 2005-10-01 00:00:00.000
2309 2005-10-01 03:44:50.000
2309 2005-10-01 08:59:00.000
2309 2005-10-01 09:29:44.000

I need it in the following format:

EmpID EventDate EventTime
2309 2005-10-01 00:00:00,03:44:50,08:59:00,09:29:44

There is no definite number of EventTime per Employee for a particular EventDate. And i don't want to use Iterative Methods.

Thanks & Regards
Rajan

Hi Rajan,

If you have access to sql server magazine take a look at this article, http://www.sqlmag.com/Article/ArticleID/93907/sql_server_93907.html

You can see the code even if you don't have membership so should still be useful. Listing 3 will probably be the most useful, although it is not particularly efficient.

Hope this helps.

Chris

|||

Another alternative might be something like:

declare @.mockup table
( EmployeeId integer,
EventDate datetime
)
insert into @.mockup values (2308, '1/1/2005')
insert into @.mockup values (2309, '10/1/2005')
insert into @.mockup values (2309, '10/1/2005 3:44:50')
insert into @.mockup values (2309, '10/1/2005 8:59')
insert into @.mockup values (2309, '10/1/2005 9:29:44')
insert into @.mockup values (2310, '11/1/2005')

select employeeId,
replace(replace(
( select e.eventDate as [data()]
from @.mockup e
where a.employeeId = e.employeeId
order by e.eventDate
for xml path ('')
), ' ', ','), 'T', ' ')
as EventDates
from @.mockup a
group by employeeId
order by employeeId

-- employeeId EventDates
-- -- -
-- 2308 2005-01-01 00:00:00
-- 2309 2005-10-01 00:00:00,2005-10-01 03:44:50,2005-10-01 08:59:00,2005-10-01 09:29:44
-- 2310 2005-11-01 00:00:00