Michael Swierczek wrote:
> On 4/25/07, Michael Swierczek <mike.swierczek@gmail.com> wrote:
>> Joe,
>> That's exactly what I needed. I completely missed that it would
>> be possible with the tablefunc/crosstab. Since you're the main
>> (only?) name I see associated with that code, I'm sure you would know.
>>
>> -Mike
>>
>
> I spoke too soon, there's an additional factor at play that
> complicates things.
> Most of the questions take a single answer, but the multiple select
> questions can have several answers. So for a regular question with
> code 'drug', a given survey_event can have 0 or 1 entries in the
> answer table with question_code 'drug'. For a multiple select
> question like, 'health', 0-7 entries are possible in the answer table,
> and we want them exported as columns 'health0', 'health1', 'health2'
> through 'health7' and each column populated according to whether that
> section of the multiple select was chosen.
>
> However, I fear I am crossing from "I have a novice question" into
> "This is complicated enough that requesting help is exploiting the
> generosity of the community for free consulting services." I won't
> pursue it any further here.
Sometimes, in cases like this, where there is business logic
intermingled with database logic, the best option is to move your
reporting code to the client. While this may result in a performance
hit, more than a single query, and some data structures slightly more
complex than rows in a table, the flexibility of this route might be
worth the effort.
Of course, you are using postgresql, so you can certainly write
functions within the database that return data structures more complex
than single rows from within the database. For example, you could write
a function using pl/perl or pl/python (or java, etc.) that returns an
XML chunk that represents the report results and then use XSLT on the
client side to format that result into whatever you like. The
possibilities are pretty endless.
Sean