Name for table with metric_name and metric_value columns

database-designpivotterminologyunpivot

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?

Best Answer

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)

See also,

Related Question