select stringid, array_agg(door1||', '||door2||', '||door3) as val from (
select stringid, case when door1 = 'yes' then 'door1' end as door1
, case when door2= 'yes' then 'door2' end as door2
, case when door3 = 'yes' then 'door3' end as door3
from 'qualitytable
) as dataset
group by stringid;
должно работать. правда, если будет пустое значение, то в поле val будет, например: 'door1', , 'door3'.
Тогда можно
select stringid, string_agg(val, ', ') as val from(
select stringid , 'door1' as val from qualitytable where door1 = 'yes'
union all
select stringid, 'door2' as val from qualitytable where door2 = 'yes'
union all
select stringid, 'door3' as val from qualitytable where door3 = 'yes'
) as dataset
group by stringid
Viktoria Smirnova,
Вроде надо так:
select stringid, val from(
select stringid , 'door1' as val from qualitytable where door1 = 'yes'
union all
select stringid, 'door2' as val from qualitytable where door2 = 'yes'
union all
select stringid, 'door3' as val from qualitytable where door3 = 'yes'
) as dataset
'door1' это название столбца, а не его door1, т.е. в кавычках
select stringid, case when door1 = 'yes' then 'door1' end as door1
, case when door2= 'yes' then 'door2' end as door2
, case when door3 = 'yes' then 'door3' end as door3
from 'qualitytable
) as dataset
group by stringid;
должно работать. правда, если будет пустое значение, то в поле val будет, например: 'door1', , 'door3'.
Тогда можно
select stringid, string_agg(val, ', ') as val from(
select stringid , 'door1' as val from qualitytable where door1 = 'yes'
union all
select stringid, 'door2' as val from qualitytable where door2 = 'yes'
union all
select stringid, 'door3' as val from qualitytable where door3 = 'yes'
) as dataset
group by stringid