How to cutomize SQL table when retrieve data?

Nirodha Wickramarathna 0
Viewed 57

When I retrieve data using Select date,type,value from table1 It show the values like below.

enter image description here

But I want to customize it like below

enter image description here

Can anyone give me idea to do this?

sql postgresql
2 Answers

First we mark every time there's a change in Value using lag and ordering by Date, and then we count to create distinct groups.

select    min(date) as "From Date"
         ,max(date) as "To Date"
         ,type
         ,value
from     (
          select  *
                  ,count(chng) over(order by date) as grp
          from   (
                  select *
                         ,case when value <> lag(value) over(order by date) then 1 end as chng
                  from   t
                 ) t
         ) t
group by grp, type, value
order by 1
From Date To Date type value
2022-09-21 2022-09-24 S 10
2022-09-25 2022-09-27 S 12
2022-09-28 2022-09-30 S 10

Fiddle

DannySlor0

This can be achieved by MATCH_RECOGNIZE, not sure if all keywords are supported in postgres but it works perfectly in oracle.

with tbl as (
select '2022-09-21' as "Date", 'S' as "Type", '10' as "Value" from dual union all
select '2022-09-22', 'S', '10' from dual union all
select '2022-09-23', 'S', '10' from dual union all
select '2022-09-24', 'S', '10' from dual union all
select '2022-09-25', 'S', '12' from dual union all
select '2022-09-26', 'S', '12' from dual union all
select '2022-09-27', 'S', '12' from dual union all
select '2022-09-28', 'S', '10' from dual union all
select '2022-09-29', 'S', '10' from dual union all
select '2022-09-30', 'S', '10' from dual)

select
*
from tbl
MATCH_RECOGNIZE (
order by "Date"
measures
first(chg1."Date") as "From Date",
First(chg2."Date") as "To Date",
chg1."Type" as "Type",
chg1."Value" as "Value"
one row per match
pattern(chg1 chg3+ chg2)
define
chg1 as "Value" <> PREV("Value") or (NVL(PREV("Value"),-1) = -1),
chg2 as "Value" <> NEXT("Value") or (NVL(NEXT("Value"),-1) = -1),
chg3 as NOT (("Value" <> PREV("Value") or (NVL(PREV("Value"),-1) = -1))
OR  ("Value" <> NEXT("Value") or (NVL(NEXT("Value"),-1) = -1)))
);
useri1kr0
Related