Tags

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


T-SQL Query | [ The Company Code Puzzle ]  – In this puzzle we have to change values of A, B and CompanyCode to NULL if repeating values are there. Please check out the sample input and expected output for details.

Sample Input

A B C CompanyCode GL
1 1 1 AA 1
1 1 2 AA 2
1 1 3 AA 3
1 1 4 AA 4
2 2 1 BB 1
2 2 2 BB 2

Expected Output

A B C CompanyCode GL
1 1 1 AA 1
NULL NULL 2 NULL 2
NULL NULL 3 NULL 3
NULL NULL 4 NULL 4
2 2 1 BB 1
NULL NULL 2 NULL 2

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

CREATE TABLE [dbo].[TheCompanyCode]
(
[A] [int] NULL,
[B] [int] NULL,
[C] [int] NULL,
[CompanyCode] [varchar](100) NULL,
[GL] [varchar](100) NULL
)
GO

--Insert Data

INSERT INTO TheCompanyCode(A,B,C,CompanyCode,GL)
VALUES
(1,    1,     1,     'AA',  001),
(1,    1,     2,     'AA',  002),
(1,    1,     3,     'AA',  003),
(1,    1,     4,     'AA',  004),
(2,    2,     1,     'BB',  001),
(2,    2,     2,     'BB',  002)

--Verify Data

SELECT * FROM TheCompanyCode

Update May 14 | Solution


--

/************   SOLUTION 1         ****************/



;WITH CTE AS
(
       SELECT A,B,C,CompanyCode,GL,
       ROW_NUMBER() OVER (PARTITION BY A,B,CompanyCode ORDER BY %%Physloc%%) rrnk
       FROM TheCompanyCode
)
SELECT 
        CASE WHEN rrnk = 1 THEN A ELSE NULL END A
       ,CASE WHEN rrnk = 1 THEN B ELSE NULL END B
       ,C
       ,CASE WHEN rrnk = 1 THEN CompanyCode ELSE NULL END CompanyCode
       ,GL
FROM CTE






--

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

Keep Learning

http://MSBISkills.com

Advertisements