Parse column B content into logical parts according to column A

Dmitri Ilin

I have a table like this

sessionId  |   hostname
------     |   ------
a1         |   domain1
a1         |   domain2
a2         |   domain1
a3         |   domain1
a3         |   domain2
a4         |   domain2

What I want is to build a logical table containing the follwoing

sessionId  |   only domain1 | only domain2 | domain1 OR domain2 | domain1 AND domain2 
-----------|----------------|--------------|--------------------|--------------------
a1         |   1            | 1            | 1                  | 1
a2         |   1            | 0            | 1                  | 0
a3         |   1            | 1            | 1                  | 1
a4         |   0            | 1            | 1                  | 0

I guess there's a simple solution for this, but I can't get my head over it :(

Gordon Linoff

You can use conditional aggregation:

select (case when sum(case when hostname = 'domain1' then 1 else 0 end) > 0 
             then 1 else 0
        end) as domain1,
       (case when sum(case when hostname = 'domain2' then 1 else 0 end) > 0
             then 1 else 0
        end) as domain2,
       (case when sum(case when hostname = 'domain1' then 1 else 0 end) > 0 or
                  sum(case when hostname = 'domain2' then 1 else 0 end) > 0
             then 1 else 0
        end) as either,
       (case when sum(case when hostname = 'domain1' then 1 else 0 end) > 0 and
                  sum(case when hostname = 'domain2' then 1 else 0 end) > 0
             then 1 else 0
        end) as both          
from t
group by sessionid;

Collected from the Internet

Please contact [email protected] to delete if infringement.