How to get blank value for a column where a value is repeated multiple times

sujith kumar matharasi 351 Reputation points
2020-10-06T20:23:58.78+00:00

Hi All,

Below is the code i have which is the input image and i am looking to have the output which is in the output image. Basically what i am trying to get here is any column where the text field value is repeated for multiple id's i want to make that as a blank( AAA, BBB are repeated twice so i want to make that column as blank for ID's 1,2,4,5 and as CCC is only once i want to keep it the same for ID 3)

I hope i am clear, Can someone please help me with this.

30463-input.png30572-output.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,561 Reputation points
    2020-10-06T21:32:41.033+00:00

    Please try the following T-SQL.

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Abbr CHAR(3), Code VARCHAR(100));
    INSERT INTO @tbl (Abbr, Code) VALUES
    ('abc', 'AAA'),
    ('def', 'BBB'),
    ('ghi', 'CCC'),
    ('jkl', 'AAA'),
    ('mno', 'BBB');
    -- DDL and sample data population, end
    
    ;WITH rs AS
    (
     SELECT * 
     , COUNT(Code) OVER (PARTITION BY code) AS counter
     FROM @tbl
    )
    SELECT ID, rs.Abbr
     , IIF(counter=1, code, '') AS Code 
    FROM rs
    ORDER BY ID;
    

    Output

    +----+------+------+
    | ID | Abbr | Code |
    +----+------+------+
    |  1 | abc  |      |
    |  2 | def  |      |
    |  3 | ghi  | CCC  |
    |  4 | jkl  |      |
    |  5 | mno  |      |
    +----+------+------+
    
    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Jeffrey Williams 1,886 Reputation points
    2020-10-06T21:40:17.147+00:00

    Since I could not use your images to create and test - I built my own test table. Note: you should not use reserved words as column names and 'Name' is really a bad choice for a column name.

    Declare @testTable Table (ID int, TestName nvarchar(max), TestValue nvarchar(max));
     Insert Into @testTable (ID, TestName, TestValue)
     Values (1, 'abc', 'AAA')
          , (2, 'def', 'BBB')
          , (3, 'ghi', 'CCC')
          , (4, 'jkl', 'AAA')
          , (5, 'mno', 'BBB');
    
     Select *
          , TestValue = Case When Exists (Select *
                                            From @testTable tt2
                                           Where tt2.ID <> tt.ID
                                             And tt2.TestValue = tt.TestValue)
                             Then ''
                             Else tt.TestValue
                         End
          , TestValue2 = iif(x.DupCount > 1, '', tt.TestValue)
       From @testTable                      tt
      Cross Apply (Select count(*) As DupCount 
                     From @testTable t 
                    Where t.TestValue = tt.TestValue) As x;
    

    This will show you a couple of options

    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2020-10-07T01:34:14.563+00:00

    Hi @sujith kumar matharasi ,

    Please also refer below:

    SELECT  ID,NAME,  
    CASE WHEN COUNT(TEXT) OVER(Partition BY TEXT)=1 THEN TEXT ELSE '' END TEXT  
    FROM #TEST  
    ORDER BY ID  
    

    Or:

     update A   
     set A.Text= ''  
     FROM #TEST A  
     inner join (select ID,NAME,TEXT,COUNT(TEXT) OVER(Partition BY TEXT) count from #TEST) B  
     ON A.ID=B.ID  
     WHERE B.COUNT>1  
      
     select * from #TEST  
    

    Output:

       ID NAME TEXT  
        1 abc  
        2 def  
        3 ghi CCC  
        4 jkl  
        5 mno  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Viorel 109.8K Reputation points
    2020-10-07T07:19:00.277+00:00

    It seems that you want to update the table. Then, until you find the modified answers, try this statement too:

    update t1
    set [Text] = ''
    from #Test as t1
    inner join #Test as t2 on t2.id <> t1.id and t2.[Text] = t1.[Text]
    
    0 comments No comments

  4. sujith kumar matharasi 351 Reputation points
    2020-10-07T17:16:13.96+00:00

    Thanks a lot to everyone for helping me with this, sorry i am new to this version from next time i will post the DDL as well.

    0 comments No comments