Consolidate Rows on Select

I want to condense several rows of a table using a select
statement, function, or stored procedure
For Example
mytable :
1 a
2 b
2 c
2 d
3 a
so that my select should result in
1 a
2 b,c,d
3 a
Any ideas or suggestions?
While this code references the sample database in my book (CHA2), the
pattern will work for your problem as well. In this example, the @.EventDates
variable is used to gather, or denomalize, the EventDate column:
USE CHA2
DECLARE
@.EventDates VARCHAR(1024)
SET @.EventDates = ''
SELECT @.EventDates = @.EventDates + CONVERT(VARCHAR(15), a.d,107 ) + '; '
FROM (Select DateBegin as [d] FROM Event
JOIN Tour
ON Event.TourID = Tour.TourID
WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a
SELECT Left(@.EventDates, Len(@.EventDates)-1) AS 'Outer Banks Lighthouses
Events'
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:392801c4aa4b$477a6810$a401280a@.phx.gbl...
>I want to condense several rows of a table using a select
> statement, function, or stored procedure
> For Example
> mytable :
> 1 a
> 2 b
> 2 c
> 2 d
> 3 a
> so that my select should result in
> 1 a
> 2 b,c,d
> 3 a
> Any ideas or suggestions?