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              a12
  2          Item2             b12             b12-a12
  3          Item3             c12             c12-b12

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?

>Solution :

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.