CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Mojtaba Mahamed
Mojtaba Mahamed

Reputation: 505

Concatenate field data with previous and next rows

I want to concatenate a field with previous and next rows. for example if I have this:

|ID|word|
|0 |Hi! |
|1 |How |
|2 |Are |
|3 |You |

I want to create a stored procedure with to input, 'before' and 'after' which indicate how many rows should use for previous and next of current row. for example if we have before=1 and after=1 then we'll get this

|ID|word       |
|0 |Hi! How    |
|1 |Hi! How Are|
|2 |How Are You|
|3 |Are You    |

and if we have before=2 and after=1 then we'll get this

|ID|word           |
|0 |Hi! How        |
|1 |Hi! How Are    |
|2 |Hi! How Are You|
|3 |How Are You    |

Thanks in advance

Upvotes: 2

Views: 143

Answers (2)

Behrouz Bakhtiari
Behrouz Bakhtiari

Reputation: 781

Check this: DECLARE @before TINYINT = 2 DECLARE @after TINYINT = 1 

SELECT  ISNULL(( SELECT STUFF((SELECT   ISNULL(TblBefore.word, '') + ' '
                               FROM     Tbl TblBefore
                               WHERE    TblBefore.Id BETWEEN T.Id - @before AND T.Id
                                        - 1
                        FOR   XML PATH('') ,
                                  TYPE).value('.', 'varchar(max)'), 1, 0, '') AS ChildValues
               ), '') + T.Word + ' '
        + ISNULL(( SELECT   STUFF((SELECT   ISNULL(TblBefore.word, '') + ' '
                                   FROM     Tbl TblBefore
                                   WHERE    TblBefore.Id BETWEEN T.Id + 1 AND T.Id
                                            + @after
                            FOR   XML PATH('') ,
                                      TYPE).value('.', 'varchar(max)'), 1, 0,
                                  '') AS ChildValues
                 ), '')
FROM  Tbl T

Upvotes: 2

AlphaNero
AlphaNero

Reputation: 1

SELECT Columnname + ' ' + ( SELECT TOP 1 Myt2.Columnname FROM table1 Myt2 WHERE Myt2.ColumnID < t1.ColumnID
ORDER BY ColumnID ) FROM table1 Myt1 ORDER BY Myt1.ColumnID

This code snippet works for 2 levels...:) still not the complete answer. It may help to get you to nth level.

Upvotes: 0

Related Questions