How to get TWO Previous record in MySQL using CURSOR-CodePudding

On my table, I have records that contain ','.

Id           Name
1            Here is the result
2            of your examination.
3            ,  
4            New Opening for the position of 
5            PT Teacher, Science Lab.
6            ,

So in cursor If I found ',' then I want to merge the 2 rows value into the first one.

DECLARE @ID int
DECLARE @Name nvarchar(500)

DECLARE MergeCursor CURSOR FOR 
select ID,NAME from  TEST_TABLE 

OPEN MergeCursor
FETCH NEXT FROM NarrationCursor into @ID,@NAME

WHILE (@@FETCH_STATUS=0)
BEGIN 
    
    if(@Name = ',')
        select * from TEST_TABLE where ID = (select max(ID) from TEST_TABLE where ID < @ID)
    
    FETCH NEXT FROM NarrationCursor into @ID,@NAME
END

CLOSE MergeCursor
DEALLOCATE MergeCursor

IN cursor how can I get the PREVIOUS TWO-ROW And UPDATE the value in 1st row and DELETE THE 2nd and THIRD ROW. AS WELL AS UPDATE THE ID

In the End, I want to output

Id           Name
1            Here is the result of your examination.
2            New Opening for the position of PT Teacher, Science Lab.

CodePudding user response：

WITH
  grouped AS
(
  SELECT
    SUM(CASE WHEN name=',' THEN 1 ELSE 0 END)
      OVER (ORDER BY id)
        AS group_id,
    id,
    name
  FROM
    TEST_TABLE
)
SELECT
  group_id   1   AS id,
  STRING_AGG(name, ' ') WITHIN GROUP (ORDER BY id)   AS name
FROM
  grouped
WHERE
  name <> ','
GROUP BY
  group_id
ORDER BY
  group_id