getting row data to return as a cell

  • If I run a query like

    select animals from animay where id = 10

    ther results are

    cow

    turky

    chicken

    is there a way to make the results apear in one cell as:

    cow,turky,chicken

    :w00t:

  • There are a few ways to accomplish this. Here are some examples...

    Example 1

    Declare @Table Table (id int, animals varchar(10))

    Insert Into @Table

    Select 10, 'cow' UNION ALL

    Select 10, 'chicken' UNION ALL

    Select 10, 'turkey' UNION ALL

    Select 11, 'turkey'

    SELECT

    t1.ID,

    List = substring((SELECT ( ', ' + animals )

    FROM @Table t2

    WHERE t1.ID = t2.ID

    ORDER BY ID

    FOR XML PATH( '' )

    ), 3, 1000 )FROM @Table t1

    Where ID = 10

    GROUP BY ID

    Example 2

    Declare @Table Table (id int, animals varchar(10))

    Insert Into @Table

    Select 10, 'cow' UNION ALL

    Select 10, 'chicken' UNION ALL

    Select 10, 'turkey' UNION ALL

    Select 11, 'turkey'

    DECLARE @Animal VARCHAR(1000)

    SELECT @Animal = ISNULL(@Animal,'') + animals + ';'

    FROM @Table

    WHERE id = 10

    SELECT @Animal AS animals

    I personally like the XML method.

  • Thanks for the reply. I really appreciate the help. After further research here is what I went with:

    I made the following into a function, and boy does it work good.

    DECLARE @List varchar(2000)

    SELECT @List = COALESCE(@List + ',', '') + Cast(tblProductGroup.GroupDesc As varchar(15))

    FROM tblProductGroup INNER JOIN

    tblLevelProductRel ON tblProductGroup.GenGroupID = tblLevelProductRel.GroupID

    WHERE tblLevelProductRel.LevelCloneID = @LevelCloneID

    return @List

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

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