Transpose Query in PostgreSQL

Question

I've the following table:

tb_item
id_item | item_name | price
----------------------------
1       | item1     | 2000
2       | item2     | 3000
3       | item3     | 4000

and

tb_value
id_value | id_item | value_type | value
----------------------------------------
1        | 1       | bedroom    | 2
2        | 1       | bathroom   | 1
3        | 2       | bedroom    | 3
4        | 2       | bathroom   | 1
5        | 3       | bedroom    | 4
6        | 3       | bathroom   | 2

I would like to get an output like this:

item_name | price | bedroom | bathroom
---------------------------------------
item1     | 2000  | 2       | 1
item2     | 3000  | 3       | 1
item3     | 4000  | 4       | 2

I'm using PostgreSQL; what query can be used to get this output? I'm using PHP too; is there a PHP function that can do this?

@JonathanLeffler , Am I able to directly use these functions? or need an installation before? — Praditha
– Praditha, Commented Nov 22, 2011 at 8:50

Adam Wenger · Accepted Answer · 2011-11-22 14:55:47Z

2

You can also accomplish this with a couple LEFT JOIN statements

SELECT i.item_name
   , i.price
   , bed.value AS bedroom
   , bath.value AS bathroom
FROM tb_item AS i
LEFT JOIN tb_value AS bed ON i.id_item = bed.id_item
   AND bed.value_type = 'bedroom'
LEFT JOIN tb_value AS bath ON i.id_item = bath.id_item
   AND bath.value_type = 'bathroom'

edited Nov 22, 2011 at 14:55

answered Nov 22, 2011 at 13:54

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

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

3 Comments

A.H. Over a year ago

+1 for separating the JOIN conditions from the WHERE filter condition.

Adam Wenger Over a year ago

@A.H. Yes there was, sorry. I've updated it, Thank you for catching that.

Praditha Over a year ago

thanks a lot, I wanna vote up your answer, but I can't do it yet, :D

Maurice Perry · Accepted Answer · 2011-11-22 08:43:19Z

0

select item_name, price, bedroom.value as bedroom, bathroom.value as bathroom
from tb_item, tb_value as bedroom, tb_value as bathroom
where bedroom.id_item=tb_item.id_item and bedroom.value_type='bedroom'
and bathroom.id_item=tb_item.id_item and bathroom.value_type='bathroom'

answered Nov 22, 2011 at 8:43

Maurice Perry

32.8k9 gold badges72 silver badges97 bronze badges

Collectives™ on Stack Overflow

Transpose Query in PostgreSQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related