Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
WITH CTE AS (
SELECT *
,ROW_NUMBER() over(partition by a,b,companycode order by a) AS Rn
FROM TheCompanyCode)
SELECT A,B,C,CompanyCode,GL FROM CTE
WHERE Rn=1
UNION ALL
SELECT NULL,NULL,C,NULL,GL FROM CTE
WHERE Rn>1
LikeLike
Nice Raja.
LikeLike
SELECT a=case when gl >1 then Null else a end ,b=case when gl >1 then Null else b end,c=case when gl >1 then Null else c end,companycode=case when gl >1 then Null else companycode end,Gl FROM TheCompanyCode
LikeLike
SELECT
CASE WHEN A = lag(A)OVER(PARTITION BY CompanyCode ORDER BY CompanyCode)Then NULL Else A End A,
CASE WHEN B = lag(B)OVER(PARTITION BY CompanyCode ORDER BY CompanyCode)Then NULL Else B End B,
C,
CASE WHEN CompanyCode = lag(CompanyCode)OVER(PARTITION BY CompanyCode ORDER BY CompanyCode)Then NULL Else CompanyCode End CompanyCode,
GL
FROM #TheCompanyCode
LikeLike
with cte as (
SELECT
[A],
[B],
[C],
[CompanyCode],
[GL],
ROW_NUMBER() over (order by (select null)) rnmbr
FROM TheCompanyCode
)
select
IIF([A] = (LAG([A]) OVER (order by rnmbr asc)), NULL, [A]) [A],
IIF([B] = (LAG([B]) OVER (order by rnmbr asc)), NULL, [B]) [B],
[C],
IIF([CompanyCode] = (LAG([CompanyCode]) OVER (order by rnmbr asc)), NULL, [CompanyCode]) [CompanyCode],
GL
from cte
LikeLike