SQL: converting columns to rows

Question

I currently have a table that looks like this:

date ---- x ---- y ---- z
2020----- 2 ---- 4 ---- 8
2018 ---- 3 ---- 3 ---- 2
2019 ---- 1 ---- 6 ---- 0

I like to rotate this table meaning that the columns become rows like this:

date ---- metric ---- value
2020 ----    x   ----  2
2018 ----    x   ----  3
2019 ----    x   ----  1
2020 ----    y   ----  4
2018 ----    y   ----  3
2019 ----    y   ----  6
2020 ----    z   ----  8
2018 ----    z   ----  2
2019 ----    z   ----  0

If it was in python, I could do it using the pivote() or t() function. However, I am not sure how to do it with SQL. Could you please help me with that?

Thanks!

Tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2020-10-15 18:44:46 +00:00
Commented Oct 15, 2020 at 18:44 — Gordon Linoff
– Gordon Linoff, Commented Oct 15, 2020 at 18:44

Gordon Linoff · Accepted Answer · 2020-10-15 18:45:24Z

1

A canonical method is union all:

select date, 'x' as metric, x as value from t union all
select date, 'y' as metric, y as value from t union all
select date, 'z' as metric, z as value from t;

Some databases support lateral joins, which simplifies this -- and is a bit faster.

answered Oct 15, 2020 at 18:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: converting columns to rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related