Comma separated result from SQL query with aggregate

Question

Pardon my lack of knowledge about SQL query. I have two tables -

and,

I need a query to produce an output as shown below -

Is it possible?

NOTE: (before you mark it as duplicate) - I've seen lots of other questions on SO requiring to show comma separated results for simpler scenarios, but I think this one is a bit different because of the grouping on Artist and the aggregate on TotalSale.

There is nothing about grouping by artist that separates this from any of the dozens and dozens of exactly the same thing all over SO and thousands of example all over the internet. Have you tried anything because every example can do this very easily. — Sean Lange, Nov 28 '17 at 19:30
Agree with Sean, if you've seen other questions that do this, then show what you tried and why it didn't work for you. What error did you get? Otherwise your question probably will get closed as a duplicate. — Tab Alleman, Nov 28 '17 at 19:34
@SeanLange: Could you please then point me to one? Any link would do. May be it's just my lack of knowledge in SQL. And also, do you suggest that I remove the NOTE? — atiyar, Nov 28 '17 at 19:38
@SeanLange: Didn't know SQL Server has GROUP_CONCAT. Do you mean MySql GROUP_CONCAT? — atiyar, Nov 28 '17 at 20:11
It doesn't but searching for that in sql server will bring you to dozens and dozens of responses that would reveal an answer nearly identical to the one posted here by Alan Burstein. — Sean Lange, Nov 28 '17 at 20:15
What version of SQL server? Older versions use `for XML path`, `cross apply` or other methods. In 2016 `String_Agg` or other methods: https://stackoverflow.com/questions/15477743/listagg-in-sqlserver — xQbert, Nov 28 '17 at 20:17
@SeanLange: Or you could just post one by yourself before him instead of criticizing a post in comment. Thanks for your thoughtful helping guideline though. — atiyar, Nov 28 '17 at 20:21
Oracle calls this function `List_Agg()` so searching for SQL server equivalent of `List_Agg()` gets you lots of close enough examples. — xQbert, Nov 28 '17 at 20:27
I haven't criticized anything here. I was trying to convince you to do a little digging by helping you find the solution instead of handing it to you. — Sean Lange, Nov 28 '17 at 20:28
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Tab Alleman, Nov 28 '17 at 20:57

score 2 · Accepted Answer · answered Nov 28 '17 at 20:03

Simple.

-- Your sample data
declare @artist table (id int, name varchar(10));
insert @artist values (1,'A'),(2,'B');

declare @album table (id int, artistId int, name varchar(10), sold int);
insert @album values (1,1,'P',2), (2,1,'Q',3), (3,2,'X',1), (4,2,'Y',3),(5,2,'Z',2);

--solution
select 
  artist    = ar.name,
  totalSale = sum(al.sold),
  albums    = stuff(max(concatAlbums.albumList),1,1,'')
from @artist ar
join @album al on ar.id = al.artistId
cross apply   
(
    select ','+name
    from @album al2
    where al2.artistId  = ar.id
    for xml path('')
) concatAlbums(albumList)
group by ar.name;

Results:

artist     totalSale   albums
---------- ----------- -------
A          5           P,Q
B          6           X,Y,Z

@Alan Burstein: Thanks a lot :) – atiyar Nov 28 '17 at 20:22 — atiyar, Nov 28 '17 at 20:22

Comma separated result from SQL query with aggregate

1 Answers1