SQL comma-separated row with Group By clause
30,607
You want to use FOR XML PATH
construct:
SELECT ACCOUNT,
unit,
SUM(state_fee),
Stuff((SELECT ', ' + code
FROM tblmta t2
WHERE t2.ACCOUNT = t1.ACCOUNT
AND t2.unit = t1.unit
AND t2.id = '123'
FOR XML PATH('')), 1, 2, '') [Codes]
FROM tblmta t1
WHERE t1.id = '123'
GROUP BY ACCOUNT,
unit
See other examples here:
- SQL same unit between two tables needs order numbers in 1 cell
- SQL Query to get aggregated result in comma seperators along with group by column in SQL Server
Comments
-
mameesh over 4 years
I have the following query:
SELECT Account, Unit, SUM(state_fee), Code FROM tblMta WHERE MTA.Id = '123' GROUP BY Account,Unit
This of course throws an exception because the Code is not in the
group by
clause. Each state_fee has a code. How do I get this code to display in 1 record (1 code per state_fee which is multiple state_fee per unit) as a comma-separated list? I looked into different solutions on here but I couldn't find any that worked with agroup by
. -
Remus Rusanu almost 13 yearsFirst I thought projectdms simply scrapes the original article w/o attribution, but actually the site belongs to the article author
-
gpinkas about 11 yearsThe link is dead. Here is an alternative: simple-talk.com/sql/t-sql-programming/…