0

My code in postgre sql looks like this:

select
ouput.Serial ID as "Serial ID",
   case when ouput.parameters->>'10' = 'true' then 'hello' else '' end as "Hello",
   case when ouput.parameters->>'11' = 'true' then 'World' else '' end as "World"
from ouput
left join company_maintenance_entities abc on abc.id = ouput.maintenance_entity_id 
left join ouput_statuses on issue_statuses.id = ouput.status_id
where
    ouput.parameters->>'10' = 'true' or ouput.parameters->>'11' = 'true'
order by ouput.Serial ID

On output I get

Serial ID Hello World
1 hello World
2 World
3 hello
4 World
5 hello World
6 hello World

How can I make query select sure that the Hello and World columns are joined in one column separated by commas and it turns out like this?

Serial ID hello world
1 hello, world
2 world
3 hello
4 world
5 hello, world
6 hello, world
1

1 Answer 1

0

The more "usual" way of doing this is to combine rows of data, rolling them up to the level you're grouping at, something like this:

select 
  t0.Serial_ID 
, group_concat( t0.value ) as values 
from 
( select
    o.Serial_ID as "Serial ID"
  , 'hello' as value 
    end as value
  from ouput as o 
  left join company_maintenance_entities abc on o.maintenance_entity_id =  abc.id 
  left join ouput_statuses os on o.status_id = os.id 
  where o.parameters->>'10' = 'true' 
  UNION ALL
  select
    o.Serial_ID 
  , 'world' 
  from ouput as o 
  left join company_maintenance_entities abc on o.maintenance_entity_id = abc.id 
  left join ouput_statuses os on o.status_id = os.id 
  where o.parameters->>'11' = 'true' 
) t0 
group by t0.Serial_ID
order by t0.Serial_ID

This can be extended to as many values as you need and will cope with the "All, Either, Neither, None" combinations automatically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.