SQL Clone
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
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10261 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
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 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 Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84843 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 110 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 Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84843 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 124 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 Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84843 Visits: 41069
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 155 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
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 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