Switch Column Value of a Table Using Cursor in SQL Server 2008

An idea to write an article came to me yesterday when one of my friends encountered a question in an interview. I felt that it would be a good brain teaser.

Question: Swap the value of s specific column value with another. For example, if we have a table t1 having column Gender then Male should be replaced with Female and vice versa.

Note: You should have a little knowledge of Cursors and the Switch Statement in SQL Server2005, 2008 and so on.

I have taken an idea and created a table keeping the structure as in the following:

select * from customers

Here we will swap the values in the name column, like “Sachin” will be replaced by “dotnetpiper.com” and vice versa.

The output will be like the following:


I have used a cursor to do it. The reason to choose a cursor is we will fetch each row individually and perform the desired action. Here is the actual SQL query implementation:

DECLARE @name VARCHAR(50) -- database name
DECLARE DotnetPiper_Cursor CURSOR FOR
SELECT name
FROM customers

OPEN DotnetPiper_Cursor
FETCH NEXT FROM DotnetPiper_Cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
      Update customers SET name=( Case when @name='sachin' then 'dotnetpiper.com'
                                       when @name= 'dotnetpiper.com' then 'sachin'
                                        else @name
                                        End) WHERE CURRENT OF DotnetPiper_Cursor

       FETCH NEXT FROM DotnetPiper_Cursor INTO @name
END

CLOSE DotnetPiper_Cursor
DEALLOCATE DotnetPiper_Cursor

Approach 2

select * from customers
update customers set name = (case name when 'sachin' then 'dotnetpiper'
else 'sachin' end);

SQL Snippet to create table Customer table:

USE [Employee]
GO

/****** Object:  Table [dbo].[Customers]    Script Date: 08/03/2015 07:18:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Customers](
    [ID] [int] NULL,
    [Name] [varchar](50) NULL,
    [Salary] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO