2

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?

6

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,

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.