Concatenate column rows into one row with a group by

Question

Post reply

Concatenate column rows into one row with a group by

Mel3405

SSCommitted

Points: 1904
More actions
March 4, 2010 at 3:10 pm

#219637

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

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

Mel3405 SSCommitted Points: 1904 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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)

Mel3405 SSCommitted Points: 1904 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

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)

skpanuganti Mr or Mrs. 500 Points: 596 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

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)

Chandru -734144 Ten Centuries Points: 1040 More actions · Answer 8

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

Mel3405 SSCommitted Points: 1904 More actions · Answer 9

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?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

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)

Mel3405 SSCommitted Points: 1904 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

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)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

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)

Mel3405 SSCommitted Points: 1904 More actions · Answer 14

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.