Select next row

Question

I would like to concatenate two text fields from the current row with the same field from the next row

So if the table is like

field1  field2  field3

text1    text3  order1
text2    text4  order1

i would like to do this:

if (field3.current_row = field3.next_row)
     SELECT field1 + getNextRow(field1) as "Concatenated Field" FROM table

Is this possible?

Answer 1

you can do something similar to this:

create table #temp
(
    field1 varchar(50),
    field2 varchar(50)
)

insert into #temp values ('text1', 'text3')
insert into #temp values ('text2', 'text4')

;with cte as
(
    select *, row_number()over(order by field1) as rownum
    from #temp
)
SELECT *
FROM 
(
    select c1.field1 + ' ' + (SELECT field1 FROM cte c2 WHERE c2.rownum = c1.rownum + 1) as ConcField
    from cte c1
) c
where c.concfield is not null

drop table #temp