SQL unpivot function

Question

i have a table with over 100 rows and 20 columns ( store, sales, size, profit etc.) how can i unpivot into just three columns ( store, metric , value)

current state

future state

Answer 1

I recommend using cross apply:

select t.store, v.metric, v.value
from t cross apply
     (values ('size', size),
             ('sales', sales),
             ('profit', profit),
             . . .
     ) v(metric, value);

Note that this requires that the value column have compatible types.

Although you do have to list all the columns, you can generate the list using a query or a spreadsheet, simplifying the effort to write the query.