Post reply

Group by with text roll up

  • April 7, 2006 at 1:43 pm

    #168447

    Hi, What's the best way to change a dataset like this:

    Code Keyword

    11     Red

    11     Blue

    11     Yellow

    12     Black

    12     Green

    to this:

    Code Keyword

    11     Red, Blue, Yellow

    12     Black, Green

    thanks,

    Andrew

  • April 7, 2006 at 5:57 pm

    #631586

    Andrew,

    First create a user defined function that looks something like this.

    CREATE FUNCTION [dbo].[fnTextRollUp] 

         (@Code int)

    RETURNS varchar(2048)

    AS

    BEGIN

    --

    DECLARE     @Keywords   VARCHAR(2048)

    SELECT      @Keywords=  ''  

    --

    SELECT      @Keywords = @Keywords + Keyword + ' '

    FROM        MyTable

    WHERE       Code = @Code

    ORDER BY    ???

    --

    RETURN      @Keywords

    END

    Then execute the following

    SELECT

    Code,dbo.fnTextRollUp(Code) AS 'Keyword'

     

  • April 8, 2006 at 6:13 pm

    #631629

    Thanks Mike

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply