From: | Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | question regarding contrib/tablefunc |
Date: | 2005-09-13 20:27:54 |
Message-ID: | 68b5b58805091313277af05d6a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Frick | 2005-09-14 09:21:16 | 7.4.7: due to optimizing of my query logik breaks |
Previous Message | operationsengineer1 | 2005-09-13 19:35:03 | Re: 7.3.x data migration to 7.4.x - inelegant solution |