SQL query to reverse rows of a column in SQL Server

Last Reply 3 months ago By dharmendr

Posted 3 months ago

Query to reverse colomn in sql

 I have table             

Coputer

Machine

Keyboard

Input device

Monitor

Output device

Harddisk

Storage device

Required output

Coputer

Storage device

Keyboard

Output device

Monitor

Input device

Harddisk

Machine

You are viewing reply posted by: dharmendr 3 months ago.
Posted 3 months ago Modified on 3 months ago

Hi smile,

Refer below test query.

SQL

DECLARE @Test AS TABLE(Name CHAR(50),Descript CHAR(50))
INSERT INTO @Test VALUES('Coputer','Machine')
INSERT INTO @Test VALUES('Keyboard','Input device')
INSERT INTO @Test VALUES('Monitor','Output device')
INSERT INTO @Test VALUES('Harddisk','Storage device')

DECLARE @Counter INT, @TotalCount INT, @Descript CHAR(50)
SET @Counter = 1 
SET @TotalCount = (SELECT COUNT(*) FROM @Test)
SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 0)) RowNum,* INTO #Temp FROM @Test

WHILE (@Counter <= @TotalCount)
BEGIN
    SET @Descript = (SELECT Descript 
					FROM (SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 0)) RowNum,Descript 
						 FROM @Test)t 
					WHERE t.RowNum = (@TotalCount + 1) - @Counter)
    UPDATE #Temp SET Descript = @Descript WHERE RowNum = @Counter
 
    SET @Counter = @Counter + 1
    CONTINUE;
END
 
SELECT Name,Descript FROM #Temp
DROP TABLE #Temp

Output

Name Descript
Coputer Storage device
Keyboard Output device
Monitor Input device
Harddisk Machine