-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Sebastian Tennant Sent: Thursday, January 26, 2012 6:55 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: Help needed creating a view Hi list, Given an 'applications' table for a static set of courses:: user_id (integer) course_name (text) completed (boolean) how best should I go about creating an 'alumni' view with columns: user_id (integer) maths (boolean) english (boolean) . . . . . . where each of the columns (apart from user_id) is a boolean value representing whether or not user_id completed each course? Sebastian - ---------------------------------------------------------------------------- ------------ A) SELECT user_id, CASE WHEN course_name = 'Maths' THEN completed ELSE false END math_cmp, CASE WHEN course_name = 'English' THEN completed ELSE false END AS english_cmp .... FROM applications a) Expand to multiple columns and store either the default "false" or the value of "completed" into the value for the corresponding column B) SELECT user_id, CASE WHEN bool_or(math_cmp) THEN true ELSE false END AS did_math, CASE WHEN bool_or(english_cmp) THEN true ELSE false END AS did_english FROM "A" GROUP BY user_id b) Then determine whether the user_id has at least one "true" in the given column by using the "bool_or" function Dynamic columns are difficult to code in SQL. You should probably also include some kind of "OTHER COMPLETED DISCIPLINES" column to catch when you add an previously unidentified course - "course_name NOT IN ('Maths','English','...')" Also concerned with the fact that, as coded, a single complete course triggers the given flag. What happens when you want to specify that they have only completed 3 of 4 courses? Also, instead of hard-coding the "course_name" targets you may want to do something like "CASE WHEN course_name IN (SELECT course_name FROM courses WHERE course_type = 'Maths')". David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
