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

Question

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.

Answer 1

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  |      |
+----+------+------+

Answer 2

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

Answer 3

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.

Answer 4

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]

Answer 5

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.