CODEWITHSUNDEEP
postgresql
Ranjit Singh
Ranjit Singh

Reputation: 27

PGSQL selecting columns on certain conditions

In a table I have 5 columns day1, day2... day5. Records in the table can have all days set to TRUE or few days set to TRUE. Is there any way in PGSQL to select only those columns of a record which have boolean value as TRUE

Example: My table is: Course, with columns as Course Name, Day1, Day2, Day3, Day4,Day5 with record set as

English,True,False,True,False,True
German,False,False,True,True,True
French,False,True,False,True,True

What I need to display as result set is:

English,Mon,Wed,Fri
German,Wed,Thu,Fri
French,Tue,Thu,Fri

Upvotes: 0

Views: 390

Answers (2)

Abelisto
Abelisto

Reputation: 15614

Function like iif missed suddenly but you could to create it simply:

create or replace function iif(boolean, anyelement, anyelement = null) returns anyelement
  language sql
  immutable
as $$
  select case when $1 is null then null when $1 then $2 else $3 end
$$;

then:

select
  course_name,
  concat_ws(',', iif(day1,'Mon'), iif(day2,'Tue'), iif(day3,'Wed'), iif(day4,'Thu'), iif(day5, 'Fri'))
from course;

Upvotes: 0

JNevill
JNevill

Reputation: 50019

I believe something like the following should do the job. It's a bit ugly because your schema isn't the most awesome. This should work on Postgres 9+

SELECT course, string_agg(day, ',') as days_of_week
FROM
    (
        SELECT course, 'Mon' as day FROM yourtable WHERE day1 = 'True'
        UNION ALL
        SELECT course, 'Tue' as day FROM yourtable WHERE day2 = 'True'
        UNION ALL
        SELECT course, 'Wed' as day FROM yourtable WHERE day3 = 'True'
        UNION ALL
        SELECT course, 'Thu' as day FROM yourtable WHERE day4 = 'True'
        UNION ALL
        SELECT course, 'Fri' as day FROM yourtable WHERE day5 = 'True'
    ) sub

Upvotes: 1

Related Questions