This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to generate two columns respectively, count the number of even numbers and count the number of odd numbers for each row.
Input Data:
1 2 3 4 5 6 7 8 9 |
col1 col2 col3 ---- ---- ---- 1 2 9 4 1 8 3 3 7 7 0 5 9 4 9 2 7 2 8 6 4 |
Expected Output:
1 2 3 4 5 6 7 8 9 |
col1 col2 col3 EvenCount OddCount ---- ---- ---- ----------- ----------- 1 2 9 1 2 4 1 8 2 1 3 3 7 0 3 7 0 5 1 2 9 4 9 1 2 2 7 2 2 1 8 6 4 3 0 |
Create a table with data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE tbl_Numbers ( col1 TINYINT ,col2 TINYINT ,col3 TINYINT ) GO INSERT INTO tbl_Numbers VALUES (1,2,9) ,(4,1,8) ,(3,3,7) ,(7,0,5) ,(9,4,9) ,(2,7,2) ,(8,6,4) GO |
Solution:
1 2 3 4 5 6 7 8 9 10 11 |
SELECT col1 ,col2 ,col3 ,3-num AS EvenCount ,num AS OddCount FROM tbl_Numbers CROSS APPLY ( VALUES(col1%2 + col2%2 + col3%2) )t(num) |
Please try the different solution for this puzzle and share it via comment...