Postgres group by empty string question to include empty string in output

Question

I have following table in Postgres

| phone | group   | spec   |
| 1     | 1       | 'Lock' |
| 1     | 2       | 'Full' |
| 1     | 3       | 'Face' | 
| 2     | 1       | 'Lock' | 
| 2     | 3       | 'Face' | 
| 3     | 2       | 'Scan' | 

Tried this

SELECT phone, string_agg(spec, ', ')
FROM mytable
GROUP BY phone;

Need this ouput for each phone where there is empty string for missing group.

| phone | spec 
| 1     | Lock, Full, Face
| 2     | Lock, '' , Face
| 3     | '', Scan ,''

Answer 1

You need a CTE which returns all possible combinations of phone and group and a left join to the table so you can group by phone:

with cte as (
  select * 
  from (
    select distinct phone from mytable   
  ) m cross join (
    select distinct "group" from mytable  
  ) g 
)  
select c.phone, string_agg(coalesce(t.spec, ''''''), ',') spec
from cte c left join mytable t
on t.phone = c.phone and t."group" = c."group"
group by c.phone

See the demo.
Results:

| phone | spec           |
| ----- | -------------- |
| 1     | Lock,Full,Face |
| 2     | Lock,'',Face   |
| 3     | '',Scan,''     |