Concatenate values of two consecutive rows SQL

I want to write a SQL query to concatenate row values of 2 consecutive rows as described below:

I have the below table:

No.    ProductName    pSerial_No
1        Item1           a12
2        Item2           b12
3        Item3           c12

I want to have another column as below:

  No.      ProductName      pSerial_No      ConcatValue  
  1          Item1             a12              
  2          Item2             b12             b12a12
  3          Item3             c12             c12b12

The concat value is the concatenation of serial_no value in row 2 with the serial_no value is row 1, serial_no value in row 3 with the serial_no value is row 2 and so on. Since row 1 has no rows above it, the value remains as it is.

How can I achieve this?

Answer

Assuming that SerialNo defines your order, then you can use LAG() to get the value from the previous row:

SELECT  ProductName,
        Serial_No,
        CONCAT(Serial_No, LAG(Serial_No) OVER(ORDER BY Serial_No)) AS ConcatValue
FROM    YourTable;

If Serial_No doesn’t define your order, then you can amend the order by as required.