CODEWITHSUNDEEP
postgresqljoinplpgsqldynamic-sqlcrosstab
Joyal Joy
Joyal Joy

Reputation: 47

How to write a select query for displaying data on a table in another way using Postgresql?

I want to write a select query to pick data from a table which is shown in this image below,PICTURE_1 1.Table Containing Data and display it like this image in this link below, PICTURE_2 2.Result of the query

About the data: The first picture shows data logged into a table for 2 seconds from 3 IDs(1,2&3) having 2 sub IDs (aa&bb). Values and timestamp are also displayed in the picture. The table conatins only 3 column as shown in PICTURE_1. Could you guys help me write a query to display data in the table to get displayed as shown in the second image using Postgresql?. You can extract ID name using substring function. The language that Im using is plpgsql. Any ideas/logic also will be good.Thank you for your time.

Upvotes: 1

Views: 206

Answers (2)

Rahul Biswas
Rahul Biswas

Reputation: 3467

Please try this. Here row value has been shown in column wise and also use CTE.

-- PostgreSQL(v11)
WITH cte_t AS (
     SELECT LEFT(name, 1) id
          , RIGHT(name, POSITION('.' IN REVERSE(name)) - 1) t_name
          , value
          , time_stamp
     FROM test
)
SELECT id
     , time_stamp :: DATE "date"
     , time_stamp :: TIME "time"
     , MAX(CASE WHEN t_name = 'aa' THEN value END) "aa"
     , MAX(CASE WHEN t_name = 'bb' THEN value END) "bb"
FROM cte_t
GROUP BY id, time_stamp
ORDER BY date, time, id;

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=6d35047560b3f83e6c906584b23034e9

Upvotes: 1

Sagar Panchal
Sagar Panchal

Reputation: 73

Check this query dbfiddle

with cte (name, value, timeStamp) as (values 
    ('1.aa', 1, '2021-08-20 10:10:01'),
    ('2.aa', 2, '2021-08-20 10:10:01'),
    ('3.aa', 3, '2021-08-20 10:10:01'),
    ('1.bb', 4, '2021-08-20 10:10:01'),
    ('2.bb', 5, '2021-08-20 10:10:01'),
    ('3.bb', 6, '2021-08-20 10:10:01'),
    ('1.aa', 7, '2021-08-20 10:10:02'),
    ('2.aa', 8, '2021-08-20 10:10:02'),
    ('3.aa', 9, '2021-08-20 10:10:02'),
    ('1.bb', 0, '2021-08-20 10:10:02'),
    ('2.bb', 1, '2021-08-20 10:10:02'),
    ('3.bb', 2, '2021-08-20 10:10:02')
), sub_cte as (
    select split_name[1] as id, split_name[2] as name, value, tt::date as date, tt::time as time from (
        select 
            regexp_split_to_array(name, '\.') split_name, 
            value, 
            to_timestamp(timestamp, 'YYYY-MM-DD HH:MI:SS') as tt 
        from cte
    ) foo
)
select id, date, time, a.value as aa, b.value as bb from sub_cte a
left join (
    select * from sub_cte where name = 'bb'
) as b using (id, date, time)
where a.name = 'aa'

Result

 id |    date    |   time   | aa | bb 
----+------------+----------+----+----
 1  | 2021-08-20 | 10:10:01 |  1 |  4
 2  | 2021-08-20 | 10:10:01 |  2 |  5
 3  | 2021-08-20 | 10:10:01 |  3 |  6
 1  | 2021-08-20 | 10:10:02 |  7 |  0
 2  | 2021-08-20 | 10:10:02 |  8 |  1
 3  | 2021-08-20 | 10:10:02 |  9 |  2
(6 rows)

Upvotes: 1

Related Questions