Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: Group By Concatenate

    Is there a way to concatenate the values of a particular field together into one field for all the records in each group of a GROUP BY without using XML?

    Ideally, I want to accomplish this in a stored procedure. Normally, I would handle this in the front end, but my front end is Crystal ...

    For example, if my data was:
    1 "A"
    1 "B"
    1 "C"
    2 "D"
    2 "E"

    I want to group by the first value and concatenate the second value:
    1 "A,B,C"
    2 "D,E"

    Very easy using code - I simply add a function to the query in Access (not very efficient, but effective), or using recordsets in the app (which is actually very efficient) except my front end is Crystal in this case .
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    with SampleData as
    		(select 1 as ID, 'A' as Val
    		union all select 1 as ID, 'B' as Val
    		union all select 1 as ID, 'C' as Val
    		union all select 2 as ID, 'D' as Val
    		union all select 2 as ID, 'E' as Val)
    select	*
    into	#SampleData
    from	SampleData
    
    ; with OrderedData as
    			(select	ID,
    					Val,
    					ROW_NUMBER() over (Partition by ID order by Val asc) as SortOrder,
    					ROW_NUMBER() over (Partition by ID order by Val desc) as ReverseOrder
    			from	#SampleData),
    		ConcatenatedData as
    			(select	ID,
    					Convert(varchar(500), Val) as Val,
    					SortOrder,
    					ReverseOrder
    			from	OrderedData
    			where	SortOrder = 1
    		union all
    			select	OrderedData.ID,
    					convert(varchar(500), ConcatenatedData.Val + ', ' + OrderedData.Val) as Val,
    					OrderedData.SortOrder,
    					OrderedData.ReverseOrder
    			from	OrderedData
    					inner join ConcatenatedData
    						on OrderedData.ID = ConcatenatedData.ID
    						and OrderedData.SortOrder = ConcatenatedData.SortOrder + 1)
    select	ID,
    		Val
    from	ConcatenatedData
    where	ReverseOrder = 1
    order by ID
    
    drop table #SampleData
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    hmmm, very interesting. Once I have taken care of this week's fires, I will have to dig in!

    thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •