Data Visualisation - (Pivot|Transpose|Cross-tab|Matrix)

> (Data|State) Management and Processing > Data Visualization Foundation

1 - About

The pivot is typically performed by the visualization tool.

The rows and columns are interchanged.

Pivot is also known as:

  • Transpose in the linear algebra word
  • Matrix in the Microsoft world
  • Cross-tab

3 - Statistics

Cross tabulation (or crosstabs for short) is a statistical process that summarises categorical data to create a contingency table. Some entries may be weighted, unweighted tables are commonly known as pivot tables.

4 - Pivot

4.1 - SQL

SELECT cust_id, 
       MAX(decode(DAY,trunc(sysdate-0)),sales)) day1,
       MAX(decode(DAY,trunc(sysdate-1)),sales)) day2,
       MAX(decode(DAY,trunc(sysdate-6)),sales)) day7
  FROM fact,dim
 WHERE <join>
   AND DAY >= sysdate minus whatever....
  GROUP BY cust_id;

4.2 - Excel

Copy > Paste Special > Transpose

4.3 - Microsoft Report (SSRS)

5 - Documentation / Reference