0

I have a table which has the below structure.

+ ----------------------+--------------+--------+
| timeStamp             | value        | type   |
+ ----------------------+--------------+--------+
| '2010-01-14 00:00:00' | '11787.3743' | 'mean' |
| '2018-04-03 14:19:21' | '9.9908'     | 'std'  |
| '2018-04-03 14:19:21' | '11787.3743' | 'min'  |
+ ----------------------+--------------+--------+

Now i want to write a select query where i can fetch the data on the basis of type.

+ ----------------------+--------------+-------------+----------+
| timeStamp             | mean_type    | min_type    | std_type |
+ ----------------------+--------------+-------------+----------+
| '2010-01-14 00:00:00' | '11787.3743' |             |          |
| '2018-04-03 14:19:21' |              |             | '9.9908' |
| '2018-04-03 14:19:21' |              | '11787.3743 |          |
+ ----------------------+--------------+-------------+----------+

Please help me how can i do this in postgres DB by writing a query.I also want to get the data at the interval of 10 minutes only.

iftekhar iftekhar
  • 103
  • 2
  • 13

1 Answers1

4

Use CASE ... WHEN ...:

with my_table(timestamp, value, type) as (
values
    ('2010-01-14 00:00:00', 11787.3743, 'mean'),
    ('2018-04-03 14:19:21', 9.9908, 'std'),
    ('2018-04-03 14:19:21', 11787.3743, 'min')
)
select 
    timestamp,
    case type when 'mean' then value end as mean_type,
    case type when 'min' then value end as min_type,
    case type when 'std' then value end as std_type
from my_table;

      timestamp      | mean_type  |  min_type  | std_type 
---------------------+------------+------------+----------
 2010-01-14 00:00:00 | 11787.3743 |            |         
 2018-04-03 14:19:21 |            |            |   9.9908
 2018-04-03 14:19:21 |            | 11787.3743 |         
(3 rows)
klin
  • 112,967
  • 15
  • 204
  • 232
  • This doesn't look like the output the OP needs, does it? -1 since this solution is not helpful. –  Apr 18 '18 at 15:11
  • @klin : How can i make the column (mean_type/min_type/std_type) dynamic and use concat like ---- case mc.type when 'mean' then value end as concat(s.type,'-',s.height,'-',s.boom_orientation_angle,'-',s.suffix,'mean'), – iftekhar iftekhar Apr 18 '18 at 18:41
  • i am not able to use concat here..Please help! – iftekhar iftekhar Apr 18 '18 at 18:41
  • It's not possible in a simple way, as the structure of the result (the number, names and types of columns) have to be strictly defined in the query. You can use dynamic sql to create a view, like in this answer: [Turning arbitrarily many rows into columns in PostgreSQL.](https://stackoverflow.com/a/41547713/1995738) – klin Apr 18 '18 at 19:56
  • 1
    @mathguy - Would you be so kind as not vote on answers you don't understand? – Adam Piotrowski Apr 18 '18 at 21:21
  • @AdamPiotrowski - I understood the answer perfectly well. It seems I didn't understand the question too well. I thought the OP needed to pivot the input data (I even wrote an answer in that sense). When klin pointed out his answer did output what the OP wanted I looked at the *question* again and got it (at the time, the desired output wasn't as clear as it is now. after heavy editing) - but by then it was too late to retract my downvote. I did delete my answer though. Apologies to klin! –  Apr 19 '18 at 00:37
  • @klin : Any Idea how can i only get the data at the interval of 10 minutes only? – iftekhar iftekhar Apr 19 '18 at 04:54
  • @iftekhariftekhar - Adapt Gordon's answer on your another question. – klin Apr 19 '18 at 09:47
  • @mathguy - Never mind, the original question wasn't clear indeed. I've updated the answer, you can correct your voting if you want. – klin Apr 19 '18 at 09:47