How to make a select statement to return "NULLs" if the value is a repetition in SQL

Question

Lets take we have:

SELECT Name, Surname, Salary, TaxPercentage
FROM Employees

returns:

 Name |Surname     |Salary  |TaxPercentage
-------------------------------------- 
Moosa | Jacobs     | $14000 | 13.5
Temba | Martins    | $15000 | 13.5
Jack  | Hendricks  | $14000 | 13.5

I want it to return:

 Name |Surname   | Salary  |TaxPercentage
-------------------------------------------  
Moosa | Jacobs   |  $14000 | NULL
Temba | Martins  |  $15000 | NULL
Jack  | Hendricks|  $14000 | 13.5 

Since TaxPercentage's value is repeated, I want it appear only once at the end.

Answer 1

In sql server 2012 and above you can use the Lead window function to get the value of the next row. Assuming you have some way to sort the data (like an identity column), you can use this to your advantage:

SELECT Name, 
       Surname, 
       Salary, 
       CASE WHEN TaxPercentage = LEAD(TaxPercentage) OVER (ORDER BY Id) THEN 
         NULL 
       ELSE 
          TaxPercentage 
       END As TaxPercentage 
FROM Employees
ORDER BY Id

See fiddle (thanks to Lasse V. Karlsen)

Answer 2

You should have some way to order the data in order. In my example, I am using simple IDENTITY column, in your it could be primary key or date:

DECLARE @DataSource  TABLE
(
     [Name] VARCHAR(12)
    ,[Surname] VARCHAR(12)
    ,[Salary] VARCHAR(12)
    ,[TaxPercentage] DECIMAL(9,1)
    --
    ,[RowID] TINYINT IDENTITY(1,1)
);

INSERT INTO @DataSource ([Name], [Surname], [Salary], [TaxPercentage])
VALUES ('Moosa', 'Jacobs', '$14000', '13.5')
      ,('Temba', 'Martins', '$15000', '13.5')
      ,('Jack', ' Hendricks', '$14000', '13.5')
      ,('Temba', 'Martins', '$15000', '1.5')
      ,('Jack', ' Hendricks', '$14000', '1.5')
      ,('Temba', 'Martins', '$15000', '23')
      ,('Jack', ' Hendricks', '$14000', '7')
      ,('Temba', 'Martins', '$15000', '7')
      ,('Jack', ' Hendricks', '$14000', '7')

SELECT [Name]
      ,[Surname]
      ,[Salary]
      ,[TaxPercentage]
      ,NULLIF([TaxPercentage], LEAD([TaxPercentage], 1, NULL) OVER (ORDER BY [RowID])) AS [NewTaxPercentage]
FROM @DataSource;