June 8, 2012 by Pinal Dave

SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement

For the last few weeks, I have been doing Friday Puzzles and I am really loving it. Yesterday I received a very interesting question by Navneet Chaurasia on Facebook Page. He was asked this question in one of the interview questions for job. Please read the original thread for a complete idea of the conversation. I am presenting the same question here.

Puzzle

Let us assume there is a single column in the table called Gender. The challenge is to write a single update statement which will flip or swap the value in the column. For example if the value in the gender column is ‘male’ swap it with ‘female’ and if the value is ‘female’ swap it with ‘male’.

Here is the quick setup script for the puzzle.

USE tempdb GO CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10)) GO INSERT INTO SimpleTable (ID, Gender) SELECT 1, 'female' UNION ALL SELECT 2, 'male' UNION ALL SELECT 3, 'male' GO SELECT * FROM SimpleTable GO

The above query will return following result set.

The puzzle was to write a single update column which will generate following result set.

There are multiple answers to this simple puzzle. Let me show you three different ways. I am assuming that the column will have either value ‘male’ or ‘female’ only.

Method 1: Using CASE Statement

I believe this is going to be the most popular solution as we are all familiar with CASE Statement.

UPDATE SimpleTable SET Gender = CASE Gender WHEN 'male' THEN 'female' ELSE 'male' END GO SELECT * FROM SimpleTable GO

Method 2: Using REPLACE Function

I totally understand it is the not cleanest solution but it will for sure work in giving situation.

UPDATE SimpleTable SET Gender = REPLACE(('fe'+Gender),'fefe','') GO SELECT * FROM SimpleTable GO

Method 3: Using IIF in SQL Server 2012

If you are using SQL Server 2012 you can use IIF and get the same effect as CASE statement.

UPDATE SimpleTable SET Gender = IIF(Gender = 'male', 'female', 'male') GO SELECT * FROM SimpleTable GO

You can read my article series on SQL Server 2012 various functions over here.

Let us clean up.

DROP TABLE SimpleTable GO

Question to you:

I came up with three simple tricks where there is a single UPDATE statement which swaps the values in the column. Do you know any other simple trick? If yes, please post here in the comments. I will pick two random winners from all the valid answers. Winners will get 1) Print Copy of SQL Server Interview Questions and Answers 2) Free Learning Code for Online Video Courses

I will announce the winners on coming Monday.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

76 thoughts on “SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement”

Roji P Thomas | June 8, 2012 at 7:08 am

UPDATE S
SET S.Gender = D.Gender
FROM SimpleTable S
INNER JOIN SimpleTable D
ON S.Gender != D.Gender

Reply
Roji P Thomas | June 8, 2012 at 7:11 am

UPDATE S
SET S.Gender = (SELECT TOP 1 Gender FROM SimpleTable D WHERE D.Gender != S.Gender)
FROM SimpleTable S

Reply
- Girish | November 28, 2012 at 10:47 pm
  
  Can you please try this string to replace.
  
  I want to replace a Value from a record like as shown below,
  
  AASDFADFAGE,CLODFVER to True,True
  ISDRFOQUASA,GREG,FIRIEN to True,True,True
  ADADFDS,ZSERCL1VER to True,True
  DIOFFISFW,AFGREG,GGFRIEN to True,True,True
  
  With out using XML. Any suggestions please…
  
  Reply
Roji P Thomas | June 8, 2012 at 7:31 am

UPDATE SimpleTable
SET Gender = RIGHT(‘fe’+Gender, ISNULL(NULLIF(LEN(Gender)+2, 8),4))

Reply
Roji P Thomas | June 8, 2012 at 7:55 am

UPDATE SimpleTable
SET Gender = STUFF(‘fe’+Gender, 1, CHARINDEX(‘female’, gender)*4, ”)

Reply
Roji P Thomas | June 8, 2012 at 8:02 am

UPDATE SimpleTable
SET Gender = SUBSTRING(‘fe’+Gender, CHARINDEX(‘female’,Gender)*5, 7)

–That’s all for the time being :)

Reply
Roji P Thomas | June 8, 2012 at 8:10 am

–OK. Seriously the last one. and a bit elegant :)
UPDATE SimpleTable
SET Gender = ISNULL(NULLIF(‘fe’+Gender, ‘fefemale’), ‘male’)

Reply
pinaldave | June 8, 2012 at 8:25 am

@roji P Thomas,

We love you buddy! Great great solutions. Isn’t this small problem a big fun?

Reply
- Nikhildas | June 8, 2012 at 10:37 am
  
  Roji P Thomas,
  
  Absolutely.. fantastic solutions..
  
  Regards,
  
  Nikhildas
  Cochin
  
  Reply
  - Vibin M Lazar | October 30, 2012 at 4:39 pm
    
    I think the first answer is simple and more understandable…
    
    Any way thanks for new ideas.. :-)
    
    With regards,
    
    Vibin M Lazar
    Calicut
    
    Reply
Nishant | June 8, 2012 at 9:14 am

Inner join……Update Male Set M.Gender=F.Gender from simpleTable Male inner join SimpleTable Female on Male.gender!=Female.gender

Reply
Anil Purswani | June 8, 2012 at 9:21 am

Oh! not as simple as you are expecting…Could be the most complex one :-) ….but I came up with this solution (and yeah! its again includes ‘case statement’)

SELECT *
FROM SimpleTable;

WITH FemaleRows AS(
SELECT *
FROM SimpleTable
WHERE Gender = ‘female’
),
MaleRows AS(
SELECT *
FROM SimpleTable
WHERE Gender = ‘male’
)
UPDATE SimpleTable
SET Gender = CASE
WHEN st.Gender = FemaleRows.Gender THEN MaleRows.Gender
WHEN st.Gender = MaleRows.Gender THEN FemaleRows.Gender
ELSE st.Gender
END
FROM SimpleTable st,
FemaleRows,
MaleRows

SELECT *
FROM SimpleTable

Reply
Aneel | June 8, 2012 at 10:03 am

/*
Using Replace in a way that can be applied for any given values to be swapped

I’ve specifically considered ‘gent’ and ‘lady’ as there is no commonality between these words
*/

create table #temp(id int, datacolumn char(4))
insert into #temp
values(1,’gent’),(2,’lady’),(3,’lady’)

declare @value1 char(4), @value2 char(4)
set @value1 = ‘lady’
set @value2 = ‘gent’

update #temp
set datacolumn = replace(@value1 + @value2,datacolumn,”)

Reply
Aneel | June 8, 2012 at 10:07 am

/*
1. A generic solution to swap any two values
2. I’ve specifically considered ‘gent’ and ‘lady’ as there is no commanality between words. And the solution still works.
3. Lastly, I’m sure you’ll consider that the way I’m using replace is not the same way as you have shown in your example. BTW even before looking at your solution the below was the solution I came up with. :-)
*/
create table #temp(id int, datacolumn char(4))
insert into #temp
values(1,’gent’),(2,’lady’),(3,’lady’)

declare @value1 char(4), @value2 char(4)
set @value1 = ‘lady’
set @value2 = ‘gent’

update #temp
set datacolumn = replace(@value1 + @value2,datacolumn,”)

Reply
SIJIN KUMAR V P | June 8, 2012 at 10:10 am

Here it is ,By using RIGHT(),DIFFERENCE() and SOUNDEX ()

UPDATE SimpleTable
SET Gender = RIGHT((‘fe’+Gender), DIFFERENCE((Gender),SOUNDEX(Gender))*2)

Reply
SIJIN KUMAR V P | June 8, 2012 at 10:24 am

By using COALESCE() and NULLIF()

UPDATE SimpleTable
SET Gender = COALESCE(NULLIF(‘female’,Gender), ‘male’)

Reply
Varun | June 8, 2012 at 10:39 am

Using an innser Join

UPDATE T
SET T.GENDER = S.GENDER
FROM SimpleTable S INNER JOIN SimpleTable T
ON T.Gender S.Gender

Reply
harshcontractor | June 8, 2012 at 10:44 am

UPDATE S
SET S.Gender = (SELECT DISTINCT Gender FROM SimpleTable AS S1 WHERE (S.Gender S1.Gender))
FROM SimpleTable AS S

Reply
SIJIN KUMAR V P | June 8, 2012 at 10:44 am

By using SUBSTRING(),ABS() and DIFFERENCE()

UPDATE SimpleTable
SET Gender = SUBSTRING((‘female’),ABS(DIFFERENCE(‘male’,Gender)-5),6)

Reply
Navneet Chaurasia | June 8, 2012 at 10:56 am

Thank u so much Sir jee…….N all of U, for such nice solutions of my problem……..

Reply
harshcontractor | June 8, 2012 at 10:59 am

UPDATE SimpleTable
SET Gender = RIGHT(‘female’, 10 – LEN(Gender))

Reply
Hemant Yadav | June 8, 2012 at 11:08 am

Here same as Case statement

UPDATE SimpleTable SET Gender = IF(Gender = ‘Male’, Female’, IF(color =
‘Female’, ‘Male’, Gender ))

Reply
Aneel | June 8, 2012 at 11:27 am

/*
Another approach
1. Update to some default value
2. Then figure out all rows that were not updated and update them to the next value.
*/
create table #temp(id int, datacolumn char(4))

insert into #temp
values(1,’lady’),(2,’gent’),(3,’lady’)

declare @vartbl table(beforeupdate char(4), afterupdate char(4), id int)

declare @value1 char(4), @value2 char(4)
set @value1 = ‘lady’
set @value2 = ‘gent’

update #temp
set datacolumn = @value1
output deleted.datacolumn,inserted.datacolumn,inserted.id
into @vartbl

update #temp
set datacolumn = @value2
where exists(select null from @vartbl tbl where beforeupdate = afterupdate
and #temp.id = tbl.id)

Reply
Kumar Harsh | June 8, 2012 at 11:32 am

Another solution can be using computed column(non persisted).
Declare @SimpleTable TABLE (ID INT, Gender VARCHAR(10),SwapCol as
Case when gender=’male’ then ‘female’else ‘male’ End)

I think thomas first method in this post is ok.

Reply
pinaldave | June 8, 2012 at 11:45 am

Aneel excellent – keep them coming. I love how all of you participate.

Reply
Sachin Kamble | June 8, 2012 at 11:49 am

update SimpleTable
set Gender=isnull( nullif(‘male’,a)+’ ‘, ‘female’)

Reply
Dattatrey Sindol (Datta) | June 8, 2012 at 11:50 am

Good one Pinal!
I like the 2nd approach among the three :)

Reply
Nikhildas | June 8, 2012 at 11:58 am

Using CHOOSE Function in SQL Server 2012

Update SimpleTable
Set Gender = Choose(Len(Gender),”,”,”,’Female’,”,’Male’)

Reply
Durga | June 8, 2012 at 12:34 pm

UPDATE g1
SET g1.Gender = g2.Gender
FROM Gender g1
INNER JOIN Gender g2 ON g2.Gender g1.Gender

Reply
vikas | June 8, 2012 at 12:48 pm

Hi Pinal Sir, Check this answer

update s1
set s1.gender=( Select top 1 gender from simpletable s2
where s2.genders1.gender)
from simpletable s1

Reply
Saikat Mitra | June 8, 2012 at 1:16 pm

UPDATE SimpleTable
SET Gender = RIGHT(‘fe’+Gender,10-LEN(Gender))

Reply
Aneel | June 8, 2012 at 1:19 pm

/*
Another Approach: Using a dummy intermediate table.
*/
create table #temp(id int, datacolumn char(4))

insert into #temp
values(1,’lady’),(2,’gent’),(3,’lady’)

select * from #temp

declare @value1 char(4), @value2 char(4)

create table #dummy(datacolumn char(4))

set @value1 = ‘lady’
set @value2 = ‘gent’

insert into #dummy
values(@value1)

update #temp
set datacolumn = coalesce((select @value2 from #dummy where datacolumn = #temp.datacolumn),@value1)

select * from #temp

Reply
swapnil | June 8, 2012 at 1:24 pm

Select fullname,gender into #tempval1 from tblAdminManager where gender =’Male ‘

Select fullname,gender into #tempval2 from tblAdminManager where gender =’Female ‘

update #tempval1 set gender = ‘Female ‘

update #tempval2 set gender = ‘Male ‘

Select * from #tempval1
union
Select * from #tempval2

Reply
Ramkoti | June 8, 2012 at 1:42 pm

Great solutions from Pinal & Roji P Thomas. Thank u..

Reply
Aneel | June 8, 2012 at 1:43 pm

/*
Another Approach: Modified version of my previous solution. We dont even need a dummy table
*/
create table #temp(id int, datacolumn char(4))

insert into #temp
values(1,’lady’),(2,’gent’),(3,’lady’)

select * from #temp

declare @value1 char(4), @value2 char(4)
set @value1 = ‘lady’
set @value2 = ‘gent’

/*
Note that in the below subquery #temp can be replaced with any table
*/
update #temp
set datacolumn = coalesce((select top 1 @value1 from #temp t where @value2 = #temp.datacolumn),@value2)

select * from #temp

Reply
Saikat Mitra | June 8, 2012 at 3:26 pm

USE tempdb
GO
CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, ‘Ladies’
UNION ALL
SELECT 2, ‘Gents’
UNION ALL
SELECT 3, ‘Gents’
GO
SELECT *
FROM SimpleTable

DECLARE @tot AS NVARCHAR(20)

SET @tot=’LadiesGents’

UPDATE SimpleTable
SET Gender = SUBSTRING(@tot,(CHARINDEX(Gender,@tot,1)% 7)*7,LEN(@tot)-LEN(Gender)+1)

SELECT *
FROM SimpleTable

Reply
Nikhildas | June 8, 2012 at 3:27 pm

Update St
Set St.Gender = t.Gender
From SimpleTable St
Cross Apply (Select Distinct gender From SimpleTable Where St.Gender Gender) t

Reply
- Nikhildas | June 8, 2012 at 3:29 pm
  
  Update St
  Set St.Gender = t.Gender
  From SimpleTable St
  Cross Apply (Select distinct gender From SimpleTable
  Where St.Gender Gender) t
  
  Reply
Arun Singh | June 8, 2012 at 3:30 pm

Hi pinal
Here is the answer:-

UPDATE SimpleTable
SET Gender=CHOOSE (ID,’male’,'female’,'female’)
GO
SELECT * FROM SimpleTable

Reply
Luca Lombardi | June 8, 2012 at 3:57 pm

Hi Pinal
Here is my solution:

;with Swapped as (
select distinct
T2.ID,T1.Gender
from SimpleTable T1
cross join SimpleTable T2
where T1.Gender T2.Gender
)
update T
set T.Gender = S.Gender
from dbo.SimpleTable T
join Swapped S on S.ID = T.ID

Reply
Nikhildas | June 8, 2012 at 4:20 pm

Updation with MERGE Operator

MERGE SimpleTable t
USING (SELECT DISTINCT Gender FROM SimpleTable) s
ON s.Gender t.gender
WHEN MATCHED THEN
UPDATE SET Gender = s.Gender;

Reply
- DHall | June 9, 2012 at 1:09 am
  
  I like the creative use of the new SQL2012 MERGE operator, but you left out the “!=” between s.Gender and t.Gender. Here’s a revised version with a couple of additional lines that make it work even if there are values other than the two we’re swapping:
  
  MERGE SimpleTable t
  USING (SELECT DISTINCT Gender FROM SimpleTable) s
  ON s.Gender != t.Gender
  – these next 2 lines are only necessary if we’re not sure that ‘male’ and ‘female’ are the only values in the table
  AND t.Gender IN ( ‘male’, ‘female’ )
  AND s.Gender IN ( ‘male’, ‘female’ )
  WHEN MATCHED THEN
  UPDATE SET Gender = s.Gender;
  
  Reply
madhivanan | June 8, 2012 at 5:16 pm

Here are other solutions
http://beyondrelational.com/modules/2/blogs/70/posts/15191/swap-values-of-column-puzzle-from-pinal-dave.aspx

Reply
Brian Tkatch | June 8, 2012 at 7:28 pm

UPDATE
SimpleTable
SET
Gender = fe_male.Gender
FROM
SimpleTable,
(SELECT ‘male’ UNION ALL SELECT ‘female’) fe_male(Gender)
WHERE
fe_male.Gender SimpleTable.Gender

Reply
carlos_sfc | June 8, 2012 at 8:51 pm

UPDATE SimpleTable
SET gender=g.gender
FROM SimpleTable as s
INNER JOIN(SELECT DISTINCT(gender) as gender FROM SimpleTable) as g
ON s.gender != g.gender

Reply
DHall | June 9, 2012 at 12:46 am

I like many of the other submissions better than my own, but since I went to the trouble of solving the puzzle, here are my two solutions:

USE tempdb
GO
CREATE TABLE SimpleTable (ID INT, Gender VARCHAR(10))
GO
INSERT INTO SimpleTable (ID, Gender)
SELECT 1, ‘female’
UNION ALL
SELECT 2, ‘male’
UNION ALL
SELECT 3, ‘male’
– I added two rows to test that my solutions work even if the Gender column contains other values than the two we’re swapping, but these are not necessary for the solution to work
UNION ALL
SELECT 4, ‘?’
UNION ALL
SELECT 5, NULL
GO

SELECT ‘Original’ AS Test, * FROM SimpleTable ORDER BY ID

;WITH Males AS ( SELECT ID FROM SimpleTable WHERE Gender = ‘male’)
,Females AS ( SELECT ID FROM SimpleTable WHERE Gender = ‘female’)
,Swapped AS (
SELECT ID, ‘male’ AS Gender FROM Females
UNION
SELECT ID, ‘female’ AS Gender FROM Males
)
UPDATE SimpleTable
SET Gender = Swapped.Gender
FROM Swapped
WHERE SimpleTable.ID = Swapped.ID

SELECT ‘Swapped’ AS Test, * FROM SimpleTable ORDER BY ID

– now put them back with a different algorithm
UPDATE SimpleTable SET Gender = ISNULL(NULLIF(ISNULL(NULLIF(ISNULL(NULLIF(Gender, ‘male’), ‘?’), ‘female’), ‘male’), ‘?’), ‘female’)
WHERE Gender IN ( ‘male’, ‘female’ ) — this is only necessary if we’re not sure that ‘male’ and ‘female’ are the only values in the table

SELECT ‘Restored’ AS Test, * FROM SimpleTable ORDER BY ID

DROP TABLE SimpleTable

Reply
Satheesh | June 9, 2012 at 2:46 am

UPDATE #SimpleTable
SET Gender=CASE WHEN LEN(Gender)=6 THEN Right(Gender,4) ELSE ‘fe’+Gender END
GO

Reply
abhi | June 9, 2012 at 5:05 am

Awesome

Reply
Mister Magoo | June 9, 2012 at 6:03 am

I find this to be a rather pleasing solution, easily adaptable to more values and the quickest of my own variants for a few hundred thousand rows in a heap.

UPDATE SimpleTable
SET Gender=(SELECT ‘male’ WHERE Gender=’female’ UNION ALL SELECT ‘female’ WHERE Gender=’male’)

Reply
- Aneel | June 9, 2012 at 11:55 am
  
  This is cool. Similar to my approach proposed above except that you are not using colaesce and also you are not using a from clause (which I forgot is possible in SQL Server thanks to my experience in Oracle :-))…
  
  Reply
mistermagooo | June 9, 2012 at 6:06 am

I like this approach and it was the quickest of my attempts:

UPDATE SimpleTable
SET Gender=(SELECT ‘male’ WHERE Gender=’female’ UNION ALL SELECT ‘female’ WHERE Gender=’male’)

Another version (but slower) that I like stylistically is this:

UPDATE SimpleTable
SET Gender=X.NewGender
FROM (VALUES(‘male’,'female’),(‘female’,'male’)) AS X(OldGender,NewGender)
WHERE SimpleTable.Gender=X.OldGender

Reply
- Aneel | June 9, 2012 at 11:57 am
  
  The second version looks cooler :-)
  
  Reply
Aneel | June 9, 2012 at 11:43 am

/*
Another Approach: WIthout using replace. And using sheer mathematical approach.
*/
create table #temp(id int, datacolumn varchar(6))

insert into #temp
values (1,’gent’),(2,’female’),(3,’gent’)

select * from #temp

declare @value1 char(4), @value2 char(6),@temp char(16)

set @value1 = ‘gent’
set @value2 = ‘female’
set @temp = @value2 + @value1 + @value2

/*
The below substring call calculates the replace string dynamically without any hard-coded indexes or lengths
*/
update #temp
set datacolumn = SUBSTRING(@temp,CHARINDEX(datacolumn,@temp,1) + LEN(datacolumn),LEN(@value1 + @value2) – LEN(datacolumn))

select * from #temp

Reply
Sandeep Mittal | June 9, 2012 at 3:05 pm

–Solution : 1
DECLARE @TAB TABLE (ID INT, Gender VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘female’
UNION ALL SELECT 2, ‘male’
UNION ALL SELECT 3, ‘male’

DECLARE @list VARCHAR(100)
SET @list = (SELECT DISTINCT ‘,’ + Gender FROM @TAB FOR XML PATH(”))
SELECT ID, REPLACE(REPLACE(@list, ‘,’ + Gender, ”),’,',”) AS Gender
FROM @TAB

– Solution : 2 (if having even more than 2 distinct values)
–This will replace 1 value with 2, 2 with 3, 3 with n, n with 1
DECLARE @TAB TABLE (ID INT, Val VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘AAA’
UNION ALL SELECT 2, ‘BBB’
UNION ALL SELECT 3, ‘BBB’
UNION ALL SELECT 4, ‘CCC’
UNION ALL SELECT 5, ‘CCC’

;WITH CTE AS(
SELECT ROW_NUMBER() OVER (ORDER BY Val) AS ROWID , Val
FROM ( SELECT DISTINCT Val FROM @TAB ) TAB
), CTEMIN AS (SELECT TOP 1 Val FROM CTE ORDER BY ROWID)

SELECT T1.ID, COALESCE(T2.Val, CTEMIN.Val) as Val
FROM (
SELECT ID, DENSE_RANK() OVER (ORDER BY Val) AS ROWID, Val FROM @TAB
) T1 LEFT JOIN CTE T2 ON T1.ROWID = T2.ROWID -1
CROSS APPLY CTEMIN
ORDER BY T1.ID

Reply
sandeepmittal11 | June 9, 2012 at 3:07 pm

–Solution : 1
DECLARE @TAB TABLE (ID INT, Gender VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘female’
UNION ALL SELECT 2, ‘male’
UNION ALL SELECT 3, ‘male’

DECLARE @list VARCHAR(100)
SET @list = (SELECT DISTINCT ‘,’ + Gender FROM @TAB FOR XML PATH(”))
SELECT ID, REPLACE(REPLACE(@list, ‘,’ + Gender, ”),’,',”) AS Gender
FROM @TAB

– Solution : 2 (if having even more than 2 distinct values)
–This will replace 1 value with 2, 2 with 3, 3 with n, n with 1
DECLARE @TAB TABLE (ID INT, Val VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘AAA’
UNION ALL SELECT 2, ‘BBB’
UNION ALL SELECT 3, ‘BBB’
UNION ALL SELECT 4, ‘CCC’
UNION ALL SELECT 5, ‘CCC’

;WITH CTE AS(
SELECT ROW_NUMBER() OVER (ORDER BY Val) AS ROWID , Val
FROM ( SELECT DISTINCT Val FROM @TAB ) TAB
), CTEMIN AS (SELECT TOP 1 Val FROM CTE ORDER BY ROWID)

SELECT T1.ID, COALESCE(T2.Val, CTEMIN.Val) as Val
FROM (
SELECT ID, DENSE_RANK() OVER (ORDER BY Val) AS ROWID, Val FROM @TAB
) T1 LEFT JOIN CTE T2 ON T1.ROWID = T2.ROWID -1
CROSS APPLY CTEMIN
ORDER BY T1.ID

Reply
sandeepmittal11 | June 9, 2012 at 3:24 pm

DECLARE @TAB TABLE (ID INT, Gender VARCHAR(10))
INSERT INTO @TAB
SELECT 1, ‘female’
UNION ALL SELECT 2, ‘male’
UNION ALL SELECT 3, ‘male’

UPDATE T1
SET T1.Gender = T2.Gender
FROM (
SELECT DENSE_RANK() OVER (ORDER BY Gender) AS ROWID , ID, Gender
FROM @TAB
) T1 INNER JOIN (
SELECT Gender, ROW_NUMBER() OVER (ORDER BY Gender DESC) AS ROWID
FROM ( SELECT DISTINCT Gender FROM @TAB) T
) T2 ON T1.ROWID = T2.ROWID

SELECT * FROM @TAB

Reply
Eric Bradford | June 11, 2012 at 5:41 pm

WITH Swap
AS (SELECT ‘male’ AS Gender
UNION ALL
SELECT ‘female’)
UPDATE T1
SET gender = Swap.Gender
FROM SimpleTable T1
CROSS JOIN Swap
WHERE Swap.Gender T1.Gender

Reply
- Eric Bradford | June 11, 2012 at 5:47 pm
  
  WHERE Swap.Gender T1.Gender
  
  Problem submitting code. The different then symbol is interpreted as empty HTML tag
  
  Reply
Brian Tkatch | June 11, 2012 at 9:25 pm

UPDATE
SimpleTable
SET
Gender = SUBSTRING(‘fe’+ Gender, POWER(LEN(Gender) % 4, 2) + 1, 6)

Reply
Pingback: SQL SERVER – Solution of Puzzle – Swap Value of Column Without Case Statement « SQL Server Journey with SQL Authority
Pingback: SQL SERVER – Simple Explanation and Puzzle with SOUNDEX Function and DIFFERENCE Function « SQL Server Journey with SQL Authority
Saurabh Maurya | June 19, 2012 at 4:37 pm

Below also works well and can be implemented for more than one columns if to be swapped-
UPDATE GENDERS set gender=G2.SwappedGender
FROM GENDERS G1, (select GENDER, (CASE WHEN GENDER=’male’ THEN ‘FEMALE’ ELSE ‘MALE’ END) as SwappedGender from genders) as G2
WHERE G1.GENDER=G2.GENDER

Reply
Girish | November 28, 2012 at 3:06 am

I want to replace a Value from a record like as shown below,

AASDFADFAGE,CLOVER True,True
ISDRFOQUASA,GREG,FIRIEN True,True,True
ADADFDS,ZSERCL1VER True,True
DIOFFISFW,AFGREG,GGFRIEN True,True,True

Any suggestions please…

Reply
- Girish | November 28, 2012 at 3:29 am
  
  Sorry for the alignment,
  
  It should be like
  
  AASDFADFAGE,CLODFVER to True,True
  ISDRFOQUASA,GREG,FIRIEN to True,True,True
  ADADFDS,ZSERCL1VER to True,True
  DIOFFISFW,AFGREG,GGFRIEN to True,True,True
  
  Reply
- sandeepmittal11 | November 28, 2012 at 11:02 am
  
  CREATE FUNCTION fn_replacevalues(
  @delimited NVARCHAR(MAX),
  @delimiter NVARCHAR(100)
  ) RETURNS VARCHAR(MAX)
  AS
  BEGIN
  DECLARE @xml XML
  DECLARE @retval VARCHAR(MAX)
  SET @xml = N” + REPLACE(@delimited,@delimiter,”) + ”
  SELECT @retval = STUFF((SELECT ‘,’ + ‘True’ FROM @xml.nodes(‘/t’) as records(r) FOR XML PATH(”)),1,1,”)
  RETURN @retval
  END
  
  DECLARE @tab TABLE( val VARCHAR(MAX))
  insert into @tab VALUES
  (‘AASDFADFAGE,CLODFVER’), (‘ISDRFOQUASA,GREG,FIRIEN’),
  (‘ADADFDS,ZSERCL1VER’), (‘DIOFFISFW,AFGREG,GGFRIEN’)
  
  SELECT dbo.fn_replacevalues(val, ‘,’)
  FROM @tab
  
  Reply
  - Girish | November 28, 2012 at 8:35 pm
    
    Thanks Sandeep. i am using XML, without Function. There is a performance hit, So any other way.
    
    Reply
    - sandeepmittal11 | November 29, 2012 at 10:37 am
      
      DECLARE @tab TABLE( val VARCHAR(MAX))
      insert into @tab VALUES
      (‘AASDFADFAGE,CLODFVER’), (‘ISDRFOQUASA,GREG,FIRIEN’),
      (‘ADADFDS,ZSERCL1VER’), (‘DIOFFISFW,AFGREG,GGFRIEN’)
      
      select REPLICATE(‘True,’,LEN(val)-LEN(REPLACE(val,’,',”)))+’True’
      from @tab
      
      Reply
      - Girish | November 29, 2012 at 8:45 pm
        
        Thanks sandeep, its working. I am looking for this kind of statement.
Eric Bradford | November 29, 2012 at 12:04 am

DECLARE @tab TABLE( val VARCHAR(MAX))
insert into @tab VALUES
(‘AASDFADFAGE,CLODFVER’), (‘ISDRFOQUASA,GREG,FIRIEN’),
(‘ADADFDS,ZSERCL1VER’), (‘DIOFFISFW,AFGREG,GGFRIEN’)

;WITH CTE (val, X)
AS
(SELECT val, CHARINDEX( ‘,’, val, 1)
FROM @tab
UNION ALL
SELECT val, CHARINDEX( ‘,’, val, X+1)
FROM CTE
WHERE CHARINDEX( ‘,’, val, X+1) 0)
SELECT val, REPLACE(RTRIM(REPLICATE (‘True ‘,COUNT(*) +1)),’ ‘, ‘,’) FROM CTE
GROUP BY val

Reply
Eric Bradford | November 29, 2012 at 12:44 am

DECLARE @tab TABLE ( val VARCHAR(MAX) )
INSERT INTO @tab
VALUES ( ‘asa’ ),
( ‘AASDFADFAGE,CLODFVER’ ),
( ‘ISDRFOQUASA,GREG,FIRIEN’ ),
( ‘ADADFDS,ZSERCL1VER’ ),
( ‘DIOFFISFW,AFGREG,GGFRIEN’ );
WITH Numbers ( X )
AS ( SELECT 1
UNION ALL
SELECT x + 1
FROM Numbers
WHERE X < 1000
)
SELECT val ,
REPLACE(RTRIM(REPLICATE('True ', SUM(N) + 1)), ' ', ',') AS Result
FROM Numbers T1
CROSS APPLY ( SELECT val ,
CASE WHEN SUBSTRING(val, x, 1) = ','
THEN 1
ELSE 0
END AS N
FROM @tab
WHERE x < LEN(val)
) T2
GROUP BY VAL
OPTION ( MAXRECURSION 1000 )

Reply
- Girish | November 29, 2012 at 8:46 pm
  
  Thanks for the solution Eric,
  
  Reply
Mitesh | December 21, 2012 at 7:26 pm

USE tempdb
GO
DECLARE @SimpleTable TABLE (
ID INT, Gender VARCHAR(10)
)
INSERT INTO @SimpleTable (ID, Gender)
SELECT 1, ‘female’
UNION ALL
SELECT 2, ‘male’
UNION ALL
SELECT 3, ‘male’

Update S SET Gender = E.Gender from @SimpleTable S
CROSS JOIN (SELECT DISTINCT Gender from @SimpleTable)E
WHERE S.Gender E.Gender

select * from @SimpleTable

Reply
deepa | December 27, 2012 at 12:02 pm

nice article…..:)

Reply
Arif | February 18, 2013 at 4:18 pm

UPDATE SIMPLETABLE A
SET GENDER = (SELECT DISTINCT GENDER
FROM SIMPLETABLE B
WHERE A.GENDER B.GENDER)

Reply
Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave
Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement

Puzzle

Method 1: Using CASE Statement

Method 2: Using REPLACE Function

Method 3: Using IIF in SQL Server 2012

Question to you:

76 thoughts on “SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement”

Leave a Reply Cancel reply

Follow “Journey to SQL Authority with Pinal Dave”

Puzzle

Method 1: Using CASE Statement

Method 2: Using REPLACE Function

Method 3: Using IIF in SQL Server 2012

Question to you:

Share:

Like this:

76 thoughts on “SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement”

Leave a Reply Cancel reply

Follow “Journey to SQL Authority with Pinal Dave”