Home 2019 June SQL Puzzle: SQL Advance Query – Place NULL for repeating values

SQL Puzzle: SQL Advance Query – Place NULL for repeating values

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to place NULL for repeating values.
Input Data:

Expected Output:

Create a table with sample data:

Solution:

Please try the different solutions for this puzzle and share it via comment...

Anvesh Patel
SQL Server: Script to find the total size of the IndexesPostgreSQL: Disable Non-Durable parameters and Improve Server Performance
Comments: 4
  1. Nilesh
    July 6, 2019 at 10:48 am

    Select
    Case When C = 1 then A Else NULL END as A,
    Case When C = 1 then B Else NULL END as B,
    C,
    Case When C = 1 then Code Else NULL END as Code
    From Code

    ReplyCancel
  2. Nikhil Bhosale
    August 13, 2019 at 5:26 am

    Select
    (Case when C != ‘1’ then NULL else A end) AS A,
    (Case when C != ‘1’ then NULL else B end) AS B,
    C,
    (Case when C != ‘1’ then NULL else code end) AS code
    from code

    ReplyCancel
  3. Phanindra Suripeddi
    October 9, 2019 at 6:15 pm

    DECLARE @Code TABLE
    (
    A int
    ,B Int
    ,C int
    ,Code CHAR(1)
    )

    INSERT INTO @Code
    VALUES
    (1,1,1,’A’)
    ,(1,1,2,’A’)
    ,(1,1,3,’A’)
    ,(1,1,4,’A’)
    ,(2,2,1,’C’)
    ,(2,2,2,’C’)
    ,(2,2,3,’C’)

    ;With T As
    (
    select *, checksum(concat(A,B,Code)) chksum From @code
    )
    ,T2 as
    (
    select *,lag(chksum) Over(partition by code Order by(select null)) as lag_chksum From T
    ) select
    case When chksum = lag_chksum then NULL else A End as A
    ,case When chksum = lag_chksum then NULL else B End as B
    ,C
    ,case When chksum = lag_chksum then NULL else code End as code
    From T2

    ReplyCancel
  4. koustav
    October 30, 2019 at 9:40 am

    select
    case when rnk = 1 then A else NULL end as A
    ,case when rnk = 1 then B else NULL end AS B
    ,C
    ,case when rnk = 1 then Code else NULL end AS Code from
    (
    select *, row_number()over (partition by Code,A,B order by A) as rnk from Code
    ) T1

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

June 3, 2019 4 Comments SQL Puzzle, , , , , , , , , , ,