Re: Consolidate Rows on Select

From: Paul Nielsen (pnielsen_at_us.ci.org)
Date: 10/12/04

  • Next message: Paul Nielsen: "Re: part removal" 
    Date: Tue, 12 Oct 2004 01:44:45 -0600

    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?
  • Next message: Paul Nielsen: "Re: part removal"