Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

string concatenate in group by function with other aggregate functions

Is it possible to concatenate strings with one or more of other group by function like sum, avg, count etc .

Say I have the following table

Id Name Order Value  
1  a    1     100  
2  b    2     200  
3  c    1     300  
4  d    1     100  
5  e    2     300

Now if I want the result to be something of this sort

Order Name   Value Count  
1     a,c,d  500   3  
2     b,e    500   2  

How can i achieve the same using a query on SQL server.

like image 878
Sarah Avatar asked Apr 06 '11 22:04

Sarah


3 Answers

Sample table

create table t123 (Id int, Name varchar(10), [Order] int, Value int)
insert t123 select 
1,'a','1',100 union all select
2,'b','2',200 union all select
3,'c','1',300 union all select
4,'d','1',100 union all select
5,'e','2',300

Query for SQL Server 2005 and above

select a.[order], STUFF((
    select ','+b.name
    from t123 b
    where b.[order] = a.[order]
    order by b.name
    for xml path('a'), type).value('.','nvarchar(max)'),1,1,'') Name,
    SUM(a.value) value,
    COUNT(*) [count]
from t123 a
group by a.[order]

Output

order       Name         value       count
----------- ------------ ----------- -----------
1           a,c,d        500         3
2           b,e          500         2
like image 178
RichardTheKiwi Avatar answered Nov 13 '22 13:11

RichardTheKiwi


Try using this.

  • I placed your original query in a CTE.
  • Then I selected from it, and grouped by the order.
  • Then I cross applied with a subquery that gets a comma-delimited set of names for each order in the outer query.
  • Since I also selected the names column, I had to also group by the names column.

Like this:

;WITH cte(id, n, o, v) as (
   SELECT Id, Name, Order, Value FROM ....
)
SELECT o, names, SUM(v), COUNT(*)
FROM cte AS outer
CROSS APPLY (
    SELECT Name+',' 
    FROM cte AS inner 
    WHERE outer.o = inner.o 
    ORDER BY Name FOR XML PATH('')
) n(names)
group by o, names
like image 43
Jeff Meatball Yang Avatar answered Nov 13 '22 15:11

Jeff Meatball Yang


If you're using MS SQL Server 2005 or newer you can create user-defined aggregate functions.

MSDN: CREATE AGGREGATE (Transact-SQL)

MSDN: Invoking CLR User-Defined Aggregate Functions

like image 1
BodhiD Avatar answered Nov 13 '22 13:11

BodhiD