Reorder data from multiplexed column values to rows

I have a table organization as below where temperature measurements are all stored in one column and there are 14 different temperature samples at different depths per timestamp.

select * from water_temp order by unixtimestamp desc limit 28;

Hoping to get output in this format:

I have inherited this table format. I have tried using distinct but can't find a way to have a distinct timestamp and depth. Thinking I may have to dump the data and use python to read and reorganize in a one row per timestamp.

Is there a straightforward SQL query to get the data to list in one row per timestamp with each depth temperature as columns? I want to output the data in csv format where each row is one timestamp.

Also is there some reason to organize data in a DB in the manner? I am tying to understand. Seems to me the more intuitive/useful way would be to have one column per temperature depth and one row per timestamp.

Thanks in advance.

Is there some reason to organize data in a DB in the manner? I am tying to understand. Seems to me the more intuitive/useful way would be to have one column per temperature depth and one row per timestamp.

You organize data in a way so that your queries are faster or to make data more consumable for your purposes. If having pivoted data make them more consumable for your needs, you should go for it.

Just to give you an example, if you had to get depth values when the temperature went higher than 40, attempting to do it with the output you're requiring, would likely force you to go through unpivoting, while it would be straight simple if you used your original table.

Conversely, if you need to do some kind of reporting, and observe only temperatures for specific depths (say depth 10 and depth 100), using the input table would require you a filtering, while using the output table can give you that instantly.

What you're looking for is called "pivot", and allows you to change rows into columns. Since in the output there are 14 columns dedicated to depth (from the data I assume they're always 14 for each timestamp), you should expect to put 14 computed fields inside the SELECT statement, like:

SELECT unixtimestamp,
       ... AS depth10,
       ... AS depth20,
       ... AS depth30,
       ... AS depth40,
       ...
       ... AS depth600
FROM water_temp 

Then you realize that you actually need to select your temperatures, but only when the depth corresponds to your selected depth. Something you can do with a CASE expression (or an IF, if we want):

SELECT unixtimestamp,
       CASE WHEN depth =  10 THEN temperature END AS depth10,
       CASE WHEN depth =  20 THEN temperature END AS depth20,
       CASE WHEN depth =  30 THEN temperature END AS depth30,
       CASE WHEN depth =  40 THEN temperature END AS depth40,
       ...
       CASE WHEN depth = 600 THEN temperature END AS depth600
FROM water_temp 

And when you attempt to do that, you realize you will have a kind of squared matrix, for which unique timestamp has now 14 records, each of which with one non-null value (on the corresponding depth column).

Since you wanted one record only, for each of your timestamps, one of the best options for reducing the size of tables is aggregation (as long as it "aggregates" data). You need to aggregate by grouping on your timestamps (because you want one record for each timestamp), and using an aggregate function that can compress one non-null value with other null values, and that's either MIN or MAX. Your code then becomes:

SELECT unixtimestamp,
       MAX(CASE WHEN depth =  10 THEN temperature END) AS depth10,
       MAX(CASE WHEN depth =  20 THEN temperature END) AS depth20,
       MAX(CASE WHEN depth =  30 THEN temperature END) AS depth30,
       MAX(CASE WHEN depth =  40 THEN temperature END) AS depth40,
       ...
       MAX(CASE WHEN depth = 600 THEN temperature END) AS depth600
FROM water_temp 
GROUP BY unixtimestamp

which gives you exactly what you were looking for.