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?

You need some way to identify the *current record* and the *next record*. If you can't define the criteria for finding those records, this question can't be answered. — Yuck, Apr 05 '12 at 15:39
You may be able to do it using row_number and a triangular join. Please see the following link http://www.sqlservercentral.com/Forums/Topic483631-338-1.aspx — Chetter Hummin, Apr 05 '12 at 15:40
Do you have an ID column in the table or just field1 and field2? — Dan P, Apr 05 '12 at 15:58
@Yuck, if the ordernumber is the same in both rows, then i want to concatenate. — jorrebor, Apr 07 '12 at 08:53

score 3 · Answer 1 · answered Apr 05 '12 at 16:13

3

If you are already on SQL Server 2012, you can do the following:

SELECT field1 + lead(field1) over (order by field1) as "Concatenated Field"
from table

answered Apr 05 '12 at 16:13

Taryn · Accepted Answer · 2012-04-05T17:04:46.040

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

Select next row

2 Answers2