SQL Question

SQL Combine 2 Rows at a Time

I have a table of keywords that I want to make 2 word combinations of while keeping in sequential order (i.e. I don't want every 2 word combo, just when they are before or after one another).

I can make this work with a

WHILE
loop, but the query is bulky and not optimal since I need to use this within a larger query. My query below:

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

CREATE TABLE #Temp
(
RowNum INT
,Keyword VARCHAR(50)
)

INSERT #Temp
VALUES
(1, 'Apple'),
(2, 'Flavored'),
(3, 'Ice'),
(4, 'Tea')

IF OBJECT_ID('tempdb..#Final') IS NOT NULL
DROP TABLE #Final

CREATE TABLE #Final
(
Combined VARCHAR(101)
)

DECLARE @i INT
DECLARE @kw1 VARCHAR(50)
DECLARE @kw2 VARCHAR(50)

SET @i = 2

WHILE EXISTS (SELECT * FROM #Temp WHERE RowNum = @i)
BEGIN

SET @kw1 = (SELECT Keyword FROM #Temp WHERE RowNum = @i - 1)
SET @kw2 = (SELECT Keyword FROM #Temp WHERE RowNum = @i)

INSERT #Final
SELECT @kw1 + ' ' + @kw2

SET @i = @i + 1

END

SELECT * FROM #Final


Does anyone know of a smarter/more efficient way I can accomplish this?

Answer

You can with LEAD as the below:

SELECT * FROM
(
    SELECT
        T.Keyword + ' ' + LEAD(T.Keyword) OVER (ORDER BY RowNum) Result
    FROM #Temp T
) A
WHERE
    A.Result IS NOT NULL