SQL Server  

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:

  1. select * from customers  
table

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:
Swap value

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:
  1. DECLARE @name VARCHAR(50) -- database name    
  2. DECLARE DotnetPiper_Cursor CURSOR FOR    
  3. SELECT name   
  4. FROM customers  
  5.   
  6. OPEN DotnetPiper_Cursor     
  7. FETCH NEXT FROM DotnetPiper_Cursor INTO @name     
  8.   
  9. WHILE @@FETCH_STATUS = 0     
  10. BEGIN           
  11.       Update customers SET name=( Case when @name='sachin' then 'dotnetpiper.com'  
  12.                                        when @name= 'dotnetpiper.com' then 'sachin'  
  13.                                         else @name   
  14.                                         End) WHERE CURRENT OF DotnetPiper_Cursor  
  15.   
  16.        FETCH NEXT FROM DotnetPiper_Cursor INTO @name     
  17. END     
  18.   
  19. CLOSE DotnetPiper_Cursor     
  20. DEALLOCATE DotnetPiper_Cursor  
Approach 2
  1. select * from customers  
  2. update customers set name = (case name when 'sachin' then 'dotnetpiper'  
  3. else 'sachin' end);  
Query

SQL Snippet to create table Customer table:
  1. USE [Employee]  
  2. GO  
  3.   
  4. /****** Object:  Table [dbo].[Customers]    Script Date: 08/03/2015 07:18:12 ******/  
  5. SET ANSI_NULLS ON  
  6. GO  
  7.   
  8. SET QUOTED_IDENTIFIER ON  
  9. GO  
  10.   
  11. SET ANSI_PADDING ON  
  12. GO  
  13.   
  14. CREATE TABLE [dbo].[Customers](  
  15.     [ID] [int] NULL,  
  16.     [Name] [varchar](50) NULL,  
  17.     [Salary] [varchar](50) NULL  
  18. ) ON [PRIMARY]  
  19.   
  20. GO  
  21.   
  22. SET ANSI_PADDING OFF  
  23. GO  

 

Membership not found