I've searched the archives pretty well, but if there was a previous answer to my question, I'm not smart enough to understand it. :)
I have to replicate the Access TRANSFORM..PIVOT functionality. The documentation in <src>/contrib/tablefunc enabled me to do this with the crosstab(src SQL, cat SQL) function, but it does 90% of my work for me without the last 10%.
I get something workable, but it looks like this: SELECT * FROM crosstab ( 'SELECT questionnaire_id, question_code, answer_value FROM db WHERE test_id='X' ORDER by 1', 'SELECT DISTINCT question_code FROM db WHERE test_id='X' ORDER BY 1') AS (questionnaire_id text, <first_distinct_ question_code> text, <second_distinct_question_code> text, <third_distinct_question_code> text, ... ... <eighty_seventh_distinct_question_code> text);
I have three dozen different test_id's to parse through, and almost 1000 distinct question codes with each. If I use the format above, my crosstab queries will be HUGE.
Basically, I just want my AS (...) to contain my questionnaire_id and then the resulting list of distinct question codes. I can write a Perl or Java command line application to take the output from 'SELECT DISTINCT question_code FROM db WHERE test_id='X' ORDER BY 1' and generate the crosstab query text for me, but I would imagine there's a way to set things up so I would not have to.
I would really appreciate any help. Thanks.
-Mike
PS It's been two weeks since I started a new job that involved database work with postgreSQL. So far, I'm quite impressed with the man pages and documentation. Thank you to everyone involved.
