Post reply

sum total and concatenate string

  • May 28, 2009 at 8:13 pm

    #384678

    are there any simpler way to do this instead of nested loops?

    declare @T table

    (col1 int,

    col2 int,

    col3 char(1))

    insert into @T

    select 1, 100, 'A' union all

    select 1, 103, 'B' union all

    select 1, 106, 'C' union all

    select 1, 110, 'D' union all

    select 2, 201, 'E' union all

    select 2, 204, 'Z' union all

    select 2, 206, 'U' union all

    select 3, 306, 'G' union all

    select 3, 309, 'T'

    select * from @T

    need results:

    col1col2col3

    1419A/B/C/D

    2611E/Z/U

    3615G/T

  • May 28, 2009 at 8:34 pm

    #1001865

    Hello,

    Sorry to answer a question with a question (well, actually two and a half questions), but:-

    1) Can there be multiple occurrences of the value of Col3 for the same value in Col1, and if so do you only want Col3 value to appear once in the concatenated string?

    2) Are the possible values of Col3 in your real life situation a small set of fixed values?

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • May 28, 2009 at 8:49 pm

    #1001869

    1) all values are random, no specific pattern

    2) in my real world, col3 is char(4)

  • May 28, 2009 at 8:51 pm

    #1001870

    select col1, sum(col2), (SELECT col3 + ', ' AS [text()]

    FROM @T a

    where a.col1 = b.col1

    ORDER BY col3

    FOR XML PATH(''))

    from @T b

    group by col1

    Edit: Doh! Nested Loop :Whistling:

  • May 29, 2009 at 7:10 am

    #1002020

    Great. Thanks.

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

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