Tags
Complex SQL Challenges, Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, Swap Values of a Column Puzzle, Swap Values of a Column without case, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ Swap Value of Column with another Without Case Statement ]
The puzzle is very simple. Here you have to swap values of column. In sample input case you have only column called Val. In this column only 2 values are present ‘A’ & ‘B’. We have to update value of A with B and B with A. Please check out the sample input and expected output for details.
Sample Input
| Val |
| A |
| B |
| B |
| A |
| B |
| B |
Expected output
| Val |
| B |
| A |
| A |
| B |
| A |
| A |
Rules/Restrictions
- The solution should be should use “SELECT” statement or “CTE”.
- Please do not use CASE, REPLACE, IIF and CHOOSE.
- Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script
Use the below script to generate the source table and fill them up with the sample data.
--
CREATE TABLE Swap
(
Val VARCHAR(1)
)
GO
INSERT INTO Swap(Val) VALUES ('A'),('B'),('B'),('A'),('B'),('B')
--
|
UPDATE – 10-Apr-2015 – Solution 1
-- Solution 1 -- UPDATE s SET s.Val = s1.Val FROM Swap S INNER JOIN Swap S1 ON S.Val <> S1.Val -- |
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
select A.NewValue Val from (
select distinct a.val Value, b.val as NewValue from Swap a
join swap b on a.val b.val) A
join Swap B on B.val = A.Value
LikeLike
Cool
LikeLike
https://polldaddy.com/js/rating/rating.jsupdate #Swap
set Val= case when a.Val=’A’ then ‘B’ when a.Val =’B’ then ‘A’ end
from #Swap a
LikeLike