Query results showing rows as columns

Question

I have 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

The method of performing a pivot/crosstab query varies depending on the type of database. Please indicate if you are using Oracle, MySql, Sql-Server, etc. — dave, Sep 30 '11 at 21:06
It's postgres 8.1. The form is PHP, I'm using PDO to query the database and sending the result out to Excel using the Pear Excel Writer. Thaks-- — Jazzy, Sep 30 '11 at 21:17
But, any method will do. I'm primarily looking for the logic. — Jazzy, Oct 03 '11 at 15:24

score 1 · Answer 1 · edited May 23 '17 at 10:34

1

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

edited May 23 '17 at 10:34

Community

1
1

answered Oct 03 '11 at 18:56

dave

1,520
11
8

Thank you. I'd already read through a couple of those but had trouble applying them to my situation. Thanks for the pointers though. – Jazzy Oct 04 '11 at 16:49

Query results showing rows as columns

1 Answers1