CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
MswatiLomnyama
MswatiLomnyama

Reputation: 1386

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

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.

Upvotes: 4

Views: 121

Answers (5)

shawnt00
shawnt00

Reputation: 17935

If for some reason you can't use LEAD() then this should work:

with T as (
    SELECT
        Name, Surname, Salary, TaxPercentage,
        row_number() over (order by TaxPercentage /* ??? */) as rn
    FROM Employees
)
select
    Name, Surname, Salary,
    nullif(
        TaxPercentage,
        (select t2.rn from T as t2 where t2.rn = t.rn + 1)
    ) as TaxPercentage
from T as t

Upvotes: 1

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

Work with SQL Server >= 2008 if needed http://sqlfiddle.com/#!3/ec020/1/0

Select o.Name, o.Surname, o.Salary
, TaxPercentage = case when o.id = 1 then o.TaxPercentage else null end
From (
    Select Name, Surname, Salary, TaxPercentage
    , id = row_number() over(partition by TaxPercentage order by Name, surname, Salary) -- update order...
    From Employees as e
) as o
order by o.TaxPercentage, o.id desc

Upvotes: 0

Eralper
Eralper

Reputation: 6622

I need a column to sort rows like Id with identity column

;with cte as (
SELECT
    Id, Name, Surname, Salary, TaxPercentage,
    LEAD(TaxPercentage, 1, NULL) OVER (ORDER BY Id) AS NextValue
FROM Employees
)
select
    Id, Name, Surname, Salary,-- TaxPercentage,
    TaxPercentage = CASE WHEN TaxPercentage = NextValue THEN NULL ELSE TaxPercentage END
from cte

Please check SQL Lag() and Lead() functions for more detail on these new analytical functions

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

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)

Upvotes: 7

gotqn
gotqn

Reputation: 43656

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;

Upvotes: 1

Related Questions