Tabulate normalized data in PostgreSQL

Question

This question is similar to what I need to do, but the voted answer only partially solves the problem. Consider this table:

ID   |  TEST  |  RESULT
001  |  AAA   |  +
001  |  BBB   |  +
002  |  AAA   |  +
003  |  BBB   |  +

I would like output like this:

ID   |  AAA |  BBB
001  |  +   |  +
002  |  +   |  
003  |      |  +

The solution I mentioned above provides the same data as its output, but does not "skip over" the empty columns, as for 003. Therefore, it is not possible to determine which test a + result refers to. I'm using PostgreSQL 8.4.9.

PS: Is a solution in pure SQL possible? Would this need to be coded in PL/pgSQL instead?

Yes, TEST has a number of different values. I would just select the values I wanted to include with WHERE? The output I require at the moment only uses two of the possible TEST values, but ideally I would like to do this in future with a few columns -- maybe up to a dozen. — SabreWolfy
– SabreWolfy, Commented Nov 7, 2011 at 10:32
tablefunc (postgresql.org/docs/8.4/static/tablefunc.html) may be what I'm looking for. — SabreWolfy
– SabreWolfy, Commented Nov 7, 2011 at 10:37

Yahia · Accepted Answer · 2011-11-07 10:54:10Z

1

For a quick and dirty solution you can use this:

SELECT DISTINCT 
T.ID,
(SELECT A.RESULT FROM MyTable A WHERE A.TEST = 'AAA' AND A.ID = T.ID) AS AAA,
(SELECT B.RESULT FROM MyTable B WHERE B.TEST = 'BBB' AND B.ID = T.ID) AS BBB
FROM MyTable T;

If you need that more dynamic consider using crosstab function in the SELECT.

edited Nov 7, 2011 at 10:54

answered Nov 7, 2011 at 10:39

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SabreWolfy Over a year ago

The single quotations around AAA and BBB after AS should be removed (at least for the query to work for me). Thanks -- that query works perfectly. It takes quite a long time to run extracting two test results (26 seconds on a table of 6000 rows), but this isn't a problem at the moment -- I'm just glad to get the output I need.

Yahia Over a year ago

@SabreWolfy changed it... glad this was of help :-)

SabreWolfy Over a year ago

Thanks :) If I want to return more than one column (for example A.RESULT and A.DATE) I should look at crosstab?

Yahia Over a year ago

@SabreWolfy not sure that crosstab can do that although I would check it out anyway since it can gain you a bit more performance...

Collectives™ on Stack Overflow

Tabulate normalized data in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related