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