开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜