Query results showing rows as columns
I hav开发者_运维百科e a form that dynamically creates checkboxes. The user signs in and selects 0 to all the checkboxes. The checkbox array is generated from the table 'options'. The form submit records into 2 tables 'survey' (one record of person data) and 'selections' (the survey key and the options key (or keys)). I want to pull out a report that will show all of the possible options as column headings with a list of people from the survey table who selected that option. So my result would look like this:
FirstAid Triage Firefighting HamRadio
Sam Sue Sam
Judy Judy Bill
Bob
So all of the options show on the results, even if no one has selected it.
Structure of the tables are:
OPTIONS
option_id description
SURVEY
survey_id name
SELECTED OPTIONS
survey_id option_id
This is a simplified example of the actual data, but includes the critical pieces (I think). How can I use SQL to get the results I need?
Thanks
Disclaimer: I have not used Postgres in several years so there may be better methods of accomplishing this task than the ones that I list.
Option 1: Use Case
When
statements as was done in the below linked solution.
correct way to create a pivot table in postgresql using CASE WHEN
Option 2: Use the Crosstab
function available in the tablefunc contrib
as was done in the below linked solution.
PostgreSQL Crosstab Query
Documentation for crosstab tablefunc contrib
http://www.postgresql.org/docs/9.1/static/tablefunc.html
精彩评论