Postgres : Select columns based on row values

Question

I have 2 tables:

Table A

 Section | measure_name   |
---------+----------------|
 A       |      col1      |
 B       |      col2      |
 C       |      col3      |

Table B

 Facility | col1| col2| col3| 
----------+-----+-----+-----+
 NY       |  5  | 50  |  90 |

I want to write a query which selects measure_name of Table A from Table B.

Can you show the desired results based on the sample data you've posted? — 404
– 404, Commented Nov 21, 2018 at 18:13

Kaushik Nayak · Accepted Answer · 2018-11-22 05:47:19Z

2

It seems you want to get the the values from Table B for the columns corresponding to each measure_name

If yes, you use UNNEST() to convert the Table B into rows and then join it with table A.

with c AS
( SELECT 
       unnest(array['col1', 'col2', 'col3']) AS colname,
       unnest(array[col1, col2, col3]) AS value
FROM B
) 
SELECT a.*,c.value FROM A 
JOIN C ON a.measure_name = c.colname;

This gives you

Section measure_name value
A       col1          5
B       col2         50
C       col3         90

Demo

answered Nov 22, 2018 at 5:47

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Postgres : Select columns based on row values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related