Question 102224 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – How to get records sharing the same column values in one single row

I have a table that looks like this

Tep ID number Date Value type
ABC 1 22-09-2021 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

I want the output like this

Tep ID number Date Value type ID number -1 Value -1 type -1 total
ABC 1 22-09-2021 1.2 X 3 3.2 Y 4.4

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

Please can someone suggest how to do it?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search