SQL conditional aggregation?

user_5782261 0
Viewed 185

Let's say I have the following table:

  name virtual message
--------------------------
  a       1     'm1'
  a       1     'm2'
  a       0     'm3'
  a       0     'm4'
  b       1     'm5'
  b       0     'm6'
  c       0     'm7'

I want to group by name but only concat the message if virtual is 1.

The result I am looking for is:

name  concat_message
---------------------
a         'm1,m2'
b            'm5'
c              ''

I couldn't find a way to conditionally aggregate using string_agg.

google-bigquery sql
3 Answers

Standard SQL offers listagg() to aggregate strings. So this looks something like:

select name,
       listagg(case when virtual = 1 then message end, ',') within group (order by message)
from t
group by name;

However, most databases have different names (and syntax) for string aggregation, such as string_agg() or group_concat().

EDIT:

In BQ the syntax would be:

select name,
       string_agg(case when virtual = 1 then message end, ',')
from t
group by name;

That said, I would recommend array_agg() rather than string_agg().

user_11440350

Consider below

select name, 
  ifnull(string_agg(if(virtual=1,message,null)), '') as concat_message
from your_table
group by name

If applied to sample data in your question - output is

enter image description here

user_52219440

use xml xpath to rotate row data into a single column

 declare @temp table(name varchar(1), virtual int, message varchar(2))

 insert into @temp
 values('a'       ,1 ,    'm1'),
 ('a',       1 ,    'm2'),
 ('a',       0 ,    'm3'),
 ('a',       0 ,    'm4'),
 ('b',       1 ,    'm5'),
 ('b',       0 ,    'm6'),
 ('c',       0 ,    'm7')

 select tmp2.name, stuff((select ','+message from @temp tmp1
 where
 tmp1.virtual=1
 and tmp1.name=tmp2.name
 for xml path('')),1,1,'') result
 from @temp tmp2
 where tmp2.virtual=1
 group by tmp2.name

output:

 name   result
 a      m1,m2
 b      m5
user_40011770
Related