Search code examples
sqlunpivot

How to turn all columns into just name and value SQL (like pd.unstack(level=-1) in Python)

Suppose we have a table like:

date   element1    element2   element3....

date1    42          30           63
date2    32          23           2 
date3    23          3            15

How to turn this table into something like

date   elements   value

date1  element1    42
date2  element1    32
date3  element1    23
date1  element2    30
date1  element3 ...

It's like a kind of pivot but I don't get it. If someone knows a shortcut for this I would really appreciate it.

Thanks

Solution

  • You can use unpivot with SQL server and Oracle.

    select date, 
    elements,
    value
from yourtable
unpivot
(
  value
  for elements in (element1, element2, element3)
) unpiv