Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ Change 0 to 1 and 1 to 0 Puzzle ]

Puzzle Description

1. We have a table called ZeroOne.
2. Here we need to show 1 instead of 0 and 0 instead of 1.
3. Please check out the sample input and expected output for details.

Sample Input

Id
0
1
0
1
0
0

Expected Output

Id Ids
0 1
1 0
0 1
1 0
0 1
0 1

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • 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 ZeroOne
(
Id INT
)
GO

INSERT INTO ZeroOne VALUES (0),(1),(0),(1),(0),(0)

--

Update June 4 | Multiple Solutions – Pawan Kumar Khowal


--

SELECT *, CASE ID WHEN 0 THEN 1 ELSE 0 END Ids FROM ZeroOne

SELECT *, (Id - 1) * -1 Ids FROM ZeroOne

SELECT *, 1 - Id Ids FROM ZeroOne

SELECT *, IIF(ID=0,1,0) Ids FROM ZeroOne

SELECT Id, (Id+1/2 -1) * -1 Ids FROM ZeroOne

DECLARE @t AS INT = 1
SELECT Id, CHOOSE(@t,1,0) Ids FROM ZeroOne

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

Advertisements