0

I have this table:

+------+--------+-------+
| week | orders |  ...  |
+------+--------+-------+
|   25 |     15 |  ...  |
|   26 |     23 |  ...  |
|   27 |     56 |  ...  |
|   28 |     34 |  ...  |
|   29 |     49 |  ...  |
+------+--------+-------+

And would like to simply make each row in week become a column like this:

+--------+----+----+----+----+----+
|  week  | 25 | 26 | 27 | 28 | 29 |
+--------+----+----+----+----+----+
| orders | 15 | 23 | 56 | 34 | 49 |
+--------+----+----+----+----+----+
|   ...  |... |... |... |... |... |
+--------+----+----+----+----+----+

The query should be able to account for additional weeks, so if a week passes, there should be a new column 30 that corresponds to the 30th week.

I've been trying out different solutions to no avail.

Bagus Tesa
  • 1,317
  • 2
  • 19
  • 42
paz10s
  • 71
  • 1
  • 6
  • 1
    On the surface at least you question does not make sense from a database perspective. Could you try to elaborate on what you're trying to do since you find it relevant? To me it could look more like how you represent your data in the system that is interacting with your database, but it's hard to tell from this question. I do suspect that you do not want to solve your problem in the way you are currently attempting – Rohde Fischer Sep 29 '22 at 08:09
  • @RohdeFischer It's because there are a lot of columns on the original table, so when viewing the original table in a dashboard you'll have to scroll to the right to see all columns. If the columns became rows, it'll fit the screen better. It's also just a preference that it'll look better switching the rows and columns. – paz10s Sep 29 '22 at 08:28
  • 3
    @paz10s isn't that kind of problem falls to the application domain? i mean, how to represent the data should only concern the dashboard, not the dbms. you can loop the otherway if you wanted. though you might need multiple passes on the rows for each column, if you are working with html and wanted a proper table element. – Bagus Tesa Sep 29 '22 at 08:42
  • @BagusTesa The application we use (Metabase) doesn't have an option to switch the rows and columns, so I was hoping to find a workaround in SQL to achieve my desired result – paz10s Sep 30 '22 at 05:44
  • @paz10s you should add [tag:metabase] tag properly to avoid confusion. – Bagus Tesa Sep 30 '22 at 05:57
  • @paz10s you might want to check this [QA](https://stackoverflow.com/questions/14084503/postgresql-convert-columns-to-rows-transpose), it has similar problem to yours - but at least they have query to play with. also, metabase seems to have an [open feature request](https://github.com/metabase/metabase/issues/3769) which you can chip in. hope those helps! – Bagus Tesa Sep 30 '22 at 11:11

0 Answers0