mySQL Rotate Table 90 degrees

Question

I have a table with ~100 columns, and only one row. Each column is a date, and each entry in a column is a corresponding value. I want to query the table so that it is displayed with only 2 columns: Date, Value.

|--------|--------|--------|--------|--------|--------|
|  Date1 |  Date2 |  Date3 |  Date4 |  Date5 |  Date6 |
|--------|--------|--------|--------|--------|--------|
| Value1 | Value2 | Value3 | Value4 | Value5 | Value6 |
|--------|--------|--------|--------|--------|--------|

What I want:

|--------|--------|
|  Dates | Values |
|--------|--------|
|  Date1 | Value1 |
|--------|--------|
|  Date2 | Value2 |
|--------|--------|
|  Date3 | Value3 |
|--------|--------|
|  Date4 | Value4 |
|--------|--------|
|  Date5 | Value5 |
|--------|--------|
|  Date6 | Value6 |

~100 columns with 1 row smells pretty bad. That poor design will give you more and more trouble every time you'll try to do things. Fix your design first — Cid, Dec 01 '21 at 06:45
didn't design it, but thats what im planning on doing, after i query the data — anesongib, Dec 01 '21 at 06:46
Well, do it first. When your bike has a punctured tire, will you first inflate it and then fix it or the opposite? — Cid, Dec 01 '21 at 06:49
Gather columns list via INFORMATION_SCHEMA.COLUMNS. Build multi-UNION query text. Execute. — Akina, Dec 01 '21 at 07:09
Btw, what a terrible answer in that post that is marked as "This question already has an answer here"! — Mikhail Berlyant, Dec 01 '21 at 07:53

score 2 · Answer 1 · edited May 06 '23 at 08:24

2

You should not use this column's design .. but anyway if you need the result for the columns in separated rows you could try using UNION ALL

select 'date1', date1 
from mytable 
union all 
select 'date2', date2 
from mytable 
......
union all 
select 'date100', date100
from mytable

And as suggested in comment by @akina you could build the query dynamically using INFORMATION_SCHEMA.COLUMNS

edited May 06 '23 at 08:24

marc_s

732,580
175
1,330
1,459

answered Dec 01 '21 at 07:12

ScaisEdge

131,976
10
91
107

mySQL Rotate Table 90 degrees

1 Answers1