I have a table that looks like this

I want the output like this

Basically I want the records with the same value in TEP in the same row.

Please can someone suggest how to do it?

CodePudding user response：

If there are only ever two rows with the same TEP value, you could use something like:

select
  t1.tep, t1.id, t1.date, t1.value, t1.type
          t2.id, t2.date, t2.value, t2.type,
  t1.value   t2.value
from table t1
join table t2
where t1.tep = t2.tep
  and t1.id < t2.id

It joins the table to itself, matches on the TEP value and makes sure that the ID values are different.

CodePudding user response：

Quite straightforward using window functions, a bit verbose but I hope easy to read. the_table CTE is a mimic of the actual data table. No restriction on the number of rows with the same tep value.

with the_table (tep, id_number, tdate, value, type) as 
(
 values
 ('ABC', 1, '22-09-2021'::date, 1.2, 'X'),
 ('XYZ', 2, '22-10-2021', 3.2, 'X'),
 ('ABC', 3, '22-10-2021', 3.2, 'Y'),
 ('WSH', 4, '22-10-2021', 3.2, 'X')
)
SELECT tep, id_number, tdate, type, "id number -1", "value -1", "type -1", total 
from
(
 select 
  tep, id_number, tdate, type, 
  lead(id_number) over wa "id number -1",
  lead(value)     over wa "value -1",
  lead(type)      over wa "type -1",
  sum(value)      over wb total,
  count(*)        over wb cnt,
  row_number()    over wa pos 
 from the_table
 window wa as (partition by tep order by id_number),
        wb as (partition by tep)
) t
where cnt > 1 and pos = 1;