Unnesting in PostgreSql

Question

I have a table that has 30 columns and it keeps growing. I cannot break it into multiple tables as it causes some visualization issues on Tableau. So the solution I was looking at was to create a new table. The old looks like

id | name | city| country| metric1 | metric2|.....|metric30  
------------------------------------------------------------
1  | dgshfsshd| Bost |United Stats|sjdhsjdsjdhjs|gdshduadjd|.......|hdhsjfhsjfsjf
2  | jsghfghfg| gert |United Stats|sjdhsjdsjdhjs|gdshduadjd|.......|hdhsjfhsjfsjf
.  
.  
.

The new table format should be something like not sure if it will work (I'm open to new ideas as well)

id | name | city| country | metric name | metric value  
1  |dgfdhh| sjdh|sghshdjd|metric1|sjdsjhfsfhsjfhsjf  
2  |jagdha| qewt|shgshfgs|metric2|hfjshfjhsjfshjsfh  
.  
.  
.

Let me know if you have any more questions. I am using postgres

see this answer stackoverflow.com/questions/1128737/unpivot-and-postgresql — Vamsi Prabhala
– Vamsi Prabhala, Commented Dec 16, 2016 at 17:25
I'm not sure that this solution works, forgot to add that the metrics have interdependency that is value of metric 4 depends on metric 3 and so on Not always but sometime. How to I get around that? — Greg91
– Greg91, Commented Dec 16, 2016 at 17:38
another key word to search for solutions is UNPIVOT, unfortunately it doesn't appear postgresql has a nice unpivot function like some other platforms. So there are 2 main more obvious solutions, 1) union all with x# of selects 2) cross join to a tally table and large case statement. but the link I tagged also has some dynamic and json solutions that might be of interest — Matt
– Matt, Commented Dec 16, 2016 at 17:50

user330315 · Accepted Answer · 2018-07-19 05:41:52Z

0

You can unnest an array of those columns and use with ordinality to get the index of the column in the array

select t.id, t..name, t.city, t.country, 
       concat('metric', m.idx) as metric_name, 
       m.val as metric_value
from the_table t
  cross join lateral unnest(array[metric1,metric2,.....,metric30) with ordinality as m(val,idx);

answered Jul 19, 2018 at 5:41

user330315

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

Collectives™ on Stack Overflow

Unnesting in PostgreSql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related