Skip site navigation (1) Skip section navigation (2)

question regarding contrib/tablefunc

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 (view raw or flat)
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.

pgsql-novice by date

Next:From: Christoph FrickDate: 2005-09-14 09:21:16
Subject: 7.4.7: due to optimizing of my query logik breaks
Previous:From: operationsengineer1Date: 2005-09-13 19:35:03
Subject: Re: 7.3.x data migration to 7.4.x - inelegant solution

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group