CODEWITHSUNDEEP
sqlpostgresqlpivotpostgresql-9.4
iftekhar iftekhar
iftekhar iftekhar

Reputation: 103

Arranging the data on the basis of column value

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.

Upvotes: 0

Views: 65

Answers (1)

klin
klin

Reputation: 121754

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)

Upvotes: 4

Related Questions