Post reply

A GROUP BY question

  • March 4, 2009 at 9:13 am

    #135141

    I have data that looks like this:

    artID title catid

    53 Maximixing Building Performance 18

    53 Maximixing Building Performance 22

    53 Maximixing Building Performance 25

    53 Maximixing Building Performance 69

    53 Maximixing Building Performance 101

    53 Maximixing Building Performance 110

    53 Maximixing Building Performance 117

    53 Maximixing Building Performance 207

    82 Total Energy Retrofit 24

    82 Total Energy Retrofit 27

    82 Total Energy Retrofit 100

    82 Total Energy Retrofit 101

    82 Total Energy Retrofit 102

    82 Total Energy Retrofit 111

    I want to process this data and have it look like this:

    artID title catid

    53 Maximixing Building Performance 18, 22, 25, 69, 101, 110, 117, 207

    82 Total Energy Retrofit 24, 27, 100, 101, 102, 111

    Any tips, hints, or suggestions would be appreciated. Thank you.

  • March 4, 2009 at 10:07 am

    #953388

    Reply removed since it did not answer the question properly ... sorry about that

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 4, 2009 at 10:25 am

    #953401

    I think this thread will provide you with what you need to solve your problem.

    Let us know if it helps.

  • March 4, 2009 at 10:34 am

    #953411

    I do not know your table structure, but you can do something like the following.

    --function to concatenate categories into comma seperated list by title

    CREATE FUNCTION dbo.fn_GetCategoriesForTitle ( @TitleId int )

    RETURNS varchar(MAX)

    AS

    BEGIN

    DECLARE @Categories varchar(max)

    SELECT @Categories = COALESCE(@Categories + ', ', '') + R.CatId

    FROM ( SELECT DISTINCT

    CAST(CatId as varchar) as CatId

    FROM dbo.TitleCategories

    WHERE TitleId = @TitleId ) R

    RETURN @Categories

    END

    GO

    --return titles and comma seperated list of categories

    SELECT

    T.ArtId,

    T.Title,

    dbo.fn_GetCategoriesForTitle(T.TitleId) as CatId

    FROM dbo.Titles

  • March 4, 2009 at 12:23 pm

    #953509

    Thanks this function worked. Of course, I had to change it a bit but it is one of those lessons learned for future use. Again, Thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

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