CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-express
jorrebor
jorrebor

Reputation: 2232

Select next row

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?

Upvotes: 1

Views: 2680

Answers (2)

Taryn
Taryn

Reputation: 247680

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

Upvotes: 2

user330315
user330315

Reputation:

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

Upvotes: 3

Related Questions