Power By 专否社区

Select next row

Go To StackoverFlow.com

1

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?

2012-04-05 15:35
by jorrebor
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 2012-04-05 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.asp - Chetter Hummin 2012-04-05 15:40
Do you have an ID column in the table or just field1 and field2 - Dan P 2012-04-05 15:58
@Yuck, if the ordernumber is the same in both rows, then i want to concatenate - jorrebor 2012-04-07 08:53
@Dan P, i do have an ID - jorrebor 2012-04-07 08:53

2

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
2012-04-05 16:08
by Taryn

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
2012-04-05 16:13
by a_horse_with_no_name
Ads