Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Concatenate column rows into one row with a group by Expand / Collapse
Author
Message
Posted Thursday, March 4, 2010 3:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:17 PM
Points: 59, Visits: 209
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
Post #877218
Posted Thursday, March 4, 2010 4:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 7,161, Visits: 13,228
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
Post #877265
Posted Friday, March 5, 2010 7:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:17 PM
Points: 59, Visits: 209
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.
Post #877626
Posted Friday, March 5, 2010 8:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #878075
Posted Saturday, March 6, 2010 4:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:17 PM
Points: 59, Visits: 209
Thank you so much for that article and all the performance tips. I'll give that a try.
Post #878241
Posted Saturday, March 6, 2010 6:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #878254
Posted Sunday, March 7, 2010 9:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:06 AM
Points: 88, Visits: 62
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
Post #878332
Posted Sunday, March 7, 2010 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #878336
Posted Monday, March 8, 2010 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 1:28 AM
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
Post #878577
Posted Monday, March 8, 2010 9:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:17 PM
Points: 59, Visits: 209
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?
Post #878742
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse