# Name for table with metric_name and metric_value columns

A typical database table might look something like this:

``````date-------geography---clicks---cost---conversions
___________________________________________________
1/1/2010---Kansas------56-------12-----1
``````

When transposed, it looks like this.

``````date-----geography---metric_name---metric_value
____________________________________________
1/1/2010-Kansas------clicks--------56
1/1/2010-Kansas------cost----------12
1/1/2010-Kansas------conversions---1
``````

The transposed table works better for some use cases where the number of metrics being collected changes frequently.

Is there a standard name for a table structured like that in the transposed table?

# Unpivot Table

Being the product of a transformation, your desired form here is called an "Unpivot table". The form you're starting from here is either normalized data, or the result of a transformation itself called a "pivot."

You can pivot and unpivot without losing data. Pivoting and operations like this emerge from OLAP functionality, where they're rotations. In PostgreSQL we pivot with the `tablefunc` module. We can easily unpivot with a `CROSS JOIN LATERAL` on a `VALUES` list,

``````SELECT id, cat, value
FROM ( VALUES (1,1,2,3), (2,10,20,30) ) AS t1(id,x,y,z)
CROSS JOIN LATERAL ( VALUES ('X',x), ('Y',y), ('Z',z) )
AS t2(cat, value);
``````

Or, though slower, we use the `unnest(ARRAY[])` idiom and parallel unnest

``````SELECT id, cat, value
FROM ( VALUES (1,1,2,3), (2,10,20,30) ) AS t1(id,x,y,z)
CROSS JOIN LATERAL unnest(ARRAY['X','Y','Z'], ARRAY[x,y,z])
AS t2(cat, value);
``````

Either way you'll produce,

`````` id | cat | value
----+-----+-------
1 | X   |     1
1 | Y   |     2
1 | Z   |     3
2 | X   |    10
2 | Y   |    20
2 | Z   |    30
(6 rows)
``````