Hello , if you need to construct view with the columns math, physics ...., I think what you need is crosstab function
Regards ________________________________ From: David Johnston <pol...@yahoo.com> To: 'Sebastian Tennant' <seb...@smolny.plus.com>; pgsql-general@postgresql.org Sent: Thursday, January 26, 2012 8:50 PM Subject: Re: [GENERAL] Help needed creating a view -----Original Message----- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sebastian Tennant Sent: Thursday, January 26, 2012 6:55 AM To: pgsql-general@postgresql.org Subject: [GENERAL] 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
