How to move data from one field one row up or down in SQL-CodePudding

I would like to move Row 2's data into Row 1 how would I do that using SQL Server

ID	Data1	DATA3	DATA4	DATA6	DATA7	DATA8	DATA9
1	ABC	DEF	GHIJK
2				1,254,800	28-Feb-2018	MWK	48.00
3	LMN	OPQ	RSTUV
4				1,558,900	28-Feb-2018	MWK	49.00

I would like my results to display as follows

ID	Data1	DATA3	DATA4	DATA6	DATA7	DATA8	DATA9
1	ABC	DEF	GHIJK	1,254,800	28-Feb-2018	MWK	48.00
2
3	LMN	OPQ	RSTUV	1,558,900	28-Feb-2018	MWK	49.00
4

CodePudding user response：

A Self-join of a CTE on a row_number could work.

WITH CTE_DATA AS (
  SELECT *
  , rn = ROW_NUMBER() OVER (ORDER BY ID) 
  FROM YourTable
)
SELECT 
  c1.Data1, c1.Data3, c1.Data4
, c2.Data6, c2.Data7, c2.Data8, c2.Data9 
FROM CTE_DATA c1
LEFT JOIN CTE_DATA c2 
  ON c2.rn = c1.rn   1;

To update the table

WITH CTE_DATA AS (
  SELECT *
  , rn = ROW_NUMBER() OVER (ORDER BY ID) 
  FROM YourTable
)
UPDATE c1
SET Data6 = c2.Data6
  , Data7 = c2.Data7
  , Data8 = c2.Data8
  , Data9 = c2.Data9
FROM CTE_DATA c1
LEFT JOIN CTE_DATA c2
  ON c2.rn = c1.rn   1;

CodePudding user response：

Do a GROUP BY query, group it by (ID - 1) / 2

SELECT ID, 
       Data1 = MAX(Data1),
       Data2 = MAX(Data2),
       . . . 
       Data9 = MAX(Data9)
FROM   yourtable
GROUP BY (ID - 1) / 2