0

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

Greg91
  • 75
  • 1
  • 2
  • 11
  • What is your question? – zwol Dec 16 '16 at 17:21
  • see this answer http://stackoverflow.com/questions/1128737/unpivot-and-postgresql – Vamsi Prabhala Dec 16 '16 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 Dec 16 '16 at 17:38
  • Possible duplicate of [How to unpivot a table in PostgreSQL](http://stackoverflow.com/questions/25625342/how-to-unpivot-a-table-in-postgresql) – Matt Dec 16 '16 at 17:48
  • 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 Dec 16 '16 at 17:50

1 Answers1

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);