March 4, 2010 at 3:10 pm
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
March 4, 2010 at 4:23 pm
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
March 5, 2010 at 7:19 am
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.
March 5, 2010 at 8:53 pm
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
Change is inevitable... Change for the better is not.
March 6, 2010 at 4:16 pm
Thank you so much for that article and all the performance tips. I'll give that a try.
March 6, 2010 at 6:47 pm
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
Change is inevitable... Change for the better is not.
March 7, 2010 at 9:46 am
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
March 7, 2010 at 10:06 am
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
Change is inevitable... Change for the better is not.
March 8, 2010 at 6:57 am
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
March 8, 2010 at 9:58 am
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?
March 8, 2010 at 6:06 pm
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
Change is inevitable... Change for the better is not.
March 9, 2010 at 8:05 am
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
March 9, 2010 at 8:44 am
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
Change is inevitable... Change for the better is not.
March 9, 2010 at 8:46 am
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
Change is inevitable... Change for the better is not.
March 9, 2010 at 8:52 am
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