Create a searchable table from one that is organized as a matrix

Question

I am confronted with the following table sales:

country    2017-Q4    2017-Q3    2017-Q2    ...    1985-Q1
----------------------------------------------------------
Germany      100        200        75                10
Senegal       20         50       130               NULL
Belgium      150         66        22                 5

I cannot query this efficiently, for instance "German sales from 2017-Q3".

I would like to reformat this table as follows:

country    quarter    sales
---------------------------
Germany    2017-Q4     100
Germany    2017-Q3     200
...
Belgium    1985-Q1       5

Can someone help me out on how to accomplish this?

UNPIVOT https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot — Squirrel, Nov 29 '17 at 12:56

score 1 · Answer 1 · answered Nov 29 '17 at 12:59

I would do this using cross apply:

select v.*
from t cross apply
     (values (t.country, '2017-Q4', [2017-Q4]),
             (t.country, '2017-Q3', [2017-Q3]),
             . . .
     ) v(country, quarter, sales);

You can also do this using unpivot or a union all query. The apply syntax implements lateral joins which are quite powerful (and have many other uses). I find that unpivot is very bespoke syntax that does one thing (so it is better to learn about lateral joins).

union all is less efficient because it reads the table multiple times.

Create a searchable table from one that is organized as a matrix

1 Answers1