Concatenate column rows into one row with a group by

  • I have this data in a table

    grpledger amount

    1A015

    1A023

    1A034

    2A228

    3A333

    3A364

    I need to sum the amounts grouping by grp

    I also need to concatenate the ledgers by grp

    The result needs to look like this. How do I do this without a cursor or loop?

    grpamt ledger

    112 A01, A02, A03

    28 A22

    37 A33, A36

  • You could use the FOR XML PATH approach and a subquery (or CTE):

    DECLARE @tbl TABLE

    (

    grp INT,ledger CHAR(3), amount INT

    )

    INSERT INTO @tbl

    SELECT 1 ,'A01', 5 UNION ALL

    SELECT 1 ,'A02', 3 UNION ALL

    SELECT 1 ,'A03', 4 UNION ALL

    SELECT 2 ,'A22', 8 UNION ALL

    SELECT 3 ,'A33', 3 UNION ALL

    SELECT 3 ,'A36', 4

    ;WITH cte AS

    (

    SELECT grp ,SUM(amount) AS amnt

    FROM @tbl

    GROUP BY grp

    )

    SELECT

    t1.grp,

    amnt,

    STUFF((SELECT ', '+ ledger FROM @tbl t2 WHERE t1.grp=t2.grp ORDER BY t2.ledger

    FOR XML PATH('')),1,2,'') AS ledger

    FROM

    @tbl t1

    INNER JOIN cte ON cte.grp=t1.grp

    GROUP BY t1.grp,cte.amnt



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thank you so much, this works perfectly!!

    I support 3 environments, sql 2000, sql2005 and sql 2008.

    This report will reside in our sql 2000 server for about 6 months and then it will be migrated over to 2008.

    Until we migrate, is there a way to do this in sql 2000?

    Again, thank you.

  • Yes... please see the following article for how you can do it in SQL Server 2000 as well as some of the caveats you need to avoid to keep from taking a massive performance hit...

    http://www.sqlservercentral.com/articles/Test+Data/61572/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you so much for that article and all the performance tips. I'll give that a try.

  • Thank you for the feedback. I'd have only repeated myself if I posted a solution for you on this thread and appreciate the time you took to peruse the article (which I why I wrote an article on a very common request to begin with). Please don't hesitate to post back if you have any additional questions.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • select grp,sum([amount]) [amount],case when [grp]=1 then 'A01,A02,A03'

    when [grp]=2 then 'A22'

    else 'A33,A36' end [ledger]

    from dbo.sqlcentral

    group by grp

    -- replace 'dbo.sqlcentral' with your table name

    Thanks,

    Santosh

  • skpanuganti (3/7/2010)


    select grp,sum([amount]) [amount],case when [grp]=1 then 'A01,A02,A03'

    when [grp]=2 then 'A22'

    else 'A33,A36' end [ledger]

    from dbo.sqlcentral

    group by grp

    -- replace 'dbo.sqlcentral' with your table name

    Thanks,

    Santosh

    That certainly works for the data given but I suspect that there might be just be a few more ledger entries than what was posted. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hope this too will work ,but while coming to performace related point of view hope this query will degrade.I have posted this because it may help someone in future.

    SELECT

    distinct c.grp,sum(amount) amt,

    ledger = REPLACE(

    ( SELECT

    ledger AS [data()]

    FROM

    sample s

    WHERE

    s.grp = c.grp

    FOR XML PATH ('')

    ), ' ', ',')

    FROM

    sample c group by c.grp

    Thanks

    Chandru

  • Jeff,

    Your suggestion with the function almost worked except that I have to work with a temporary table and so I can't use the function.

    I have been trying to avoid the FOR XML function because it looks really complicated in sql 2000.

    Any other suggestions?

  • MelissaLevitt (3/8/2010)


    Jeff,

    Your suggestion with the function almost worked except that I have to work with a temporary table and so I can't use the function.

    I have been trying to avoid the FOR XML function because it looks really complicated in sql 2000.

    Any other suggestions?

    How many rows are in the table and how many rows would be concatenated for the result. Also, I need to know what the length of the ledger ID's would be. Can you post the CREATE script for the temp table, please?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you so much for taking the time to look into this.

    --Here is the temp table to load with transactions from ledgers.

    --Ledgers are phisical tables. There are 122 ledger tables and they can grow as we acquire more business units

    --Each ledger can contain approximately 1 million rows.

    create table #AmountSum (accountvarchar(15),

    orgvarchar(15),

    currencychar(15),

    amountnumeric(18,3),

    ledgerchar(3) )

    --To populate #AmountSum, I loop through all the ledgers.

    --This table will contain approx. 20,000 rows

    exec('insert

    #AmountSum

    select

    s.account,

    s.org,

    s.currency,

    sum(s.amount)

    ''' + @ldg + ''' -->> This is the Ledger

    from

    SUNDB.dbo.' + @ldg + ' s

    --join to a couple more tables for other data

    where

    s.period <= ' + @EndPeriod + '

    group by

    s.account,

    s.org,

    s.currency

    ')

    --I will aggregate once more because transactions for the same account-org combination may be spread among several ledgers.

    --It is here where I would like to concatenate the ledger(s)

    --This result will contain about 10,000 rows.

    select

    s.account,

    s.org,

    s.currency,

    sum(s.amount)

    --concatenate ledger

    from

    #AmountSum

    group by

    s.account,

    s.org,

    s.currency

  • I'm at work right now and might just be missing because I'm in a bit of a press for time, but how does what you just posted have anything to do with the concatentation problem you posted. Like I said, I might just be missing it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ah... if it was a snake, it would have bitten me...

    ]--It is here where I would like to concatenate the ledger(s)[/i]

    I'll have to take a look at this tonight.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you so much!! I will start looking at creating a loop to concatenate the ledgers and see how much time it adds to my process time. I just wanted a set of expert eyes, in case there is a better way to do this.

    Again, thank you.

Viewing 15 posts - 1 through 15 (of 19 total)

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