I have a data-set with
- a line number
- a name
- a value
I want to select the rows in line number order, but I want to consolidate consecutive rows into a single row, concatenating the names, if the value is null.
For example, here's my data:
values (
(1, 'a', 1),
(2, 'b', 2),
(3, 'c', null),
(4, 'd', null),
(5, 'e', 3),
(6, 'f', null),
(7, 'g', null),
(8, 'h', 4),
(9, 'i', null),
(10, 'j', 5)
)
I want to transform the data into this:
values (
(1, 'a', 1),
(2, 'b', 2),
(3, 'cd', null),
(4, 'e', 3),
(5, 'fg', null),
(6, 'h', 4),
(7, 'i', null),
(8, 'j', 5)
)
Below is what I came up with. The "u" table computes an amount to add to get the next logical row number; the "x" table actually computes the logical row number; finally we group by the logical row number and use string_agg to get a single name for each row.
Is there an easier way to write this query, using some window function functionality that I'm not aware of :)?
Thanks
Andrew
with
t (line_number, my_name, my_value) as (values
(1, 'a', 1),
(2, 'b', 2),
(3, 'c', null),
(4, 'd', null),
(5, 'e', 3),
(6, 'f', null),
(7, 'g', null),
(8, 'h', 4),
(9, 'i', null),
(10, 'j', 5)),
u as (
select
line_number,
my_name,
my_value,
case when lag(my_value, 1) over (order by line_number) is null then case when my_value is null then 0 else 1 end else 1 end amount_to_add
from
t),
x as (
select
line_number,
my_name,
my_value,
sum(amount_to_add) over (order by line_number) logical_line
from
u)
select
logical_line,
string_agg(my_name, ''),
my_value
from
x
group by
logical_line,
my_value
order by
logical_line