Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenate column rows into one row with a group by


Concatenate column rows into one row with a group by

Author
Message
Mel3405
Mel3405
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 220
I have this data in a table

grp   ledger    amount
1   A01      5
1   A02      3
1   A03      4
2   A22      8
3   A33      3
3   A36      4

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?

grp   amt ledger
1   12 A01, A02, A03
2   8 A22
3   7 A33, A36
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Mel3405
Mel3405
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 220
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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44886 Visits: 39856
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mel3405
Mel3405
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 220
Thank you so much for that article and all the performance tips. I'll give that a try.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44886 Visits: 39856
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
skpanuganti
skpanuganti
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 66
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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44886 Visits: 39856
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Chandru -734144
Chandru -734144
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 263
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
Mel3405
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 220
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search