SQL query to reverse rows of a column in SQL Server

Last Reply 8 months ago By dharmendr

Posted 8 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

Posted 8 months ago Modified on 8 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