sql server return null in select when value is repeated

i have a query returning user actions with action date , howeever , I want my query to return all the actions with their dates but return null for the action date when the date equals the previous date , for example

action                date
gone shopping         10/1/2011
swimming              10/1/2011
riding                11/1/2011

so i want my select to return rows like this
action                date
gone shopping         开发者_如何学C10/1/2011
swimming              NULL
riding                11/1/2011

anyone has an idea to implement this?

Presumably this is because that's how you want to display the data?

In which case, do this in your user interface code, not in your SQL queries. It is possible using PARTITION and ORDER BY but you'll end up with a very convoluted SQL query.

This sounds like a situation where you would want this aesthetic logic handled in the application layer, not the database layer.

It could cause issues if you change the order of records, or something downstream adds additional logic like a GROUP BY.

It is generally a bad idea to remove data without a reason. You will be effectively uncoupling the action from the date for any other process that consumes your data downstream.

declare @T table
(
  [action] varchar(20),
  [date] date
)

insert into @T
select 'gone shopping', '10/1/2011' union all
select 'swimming',      '10/1/2011' union all
select 'riding',        '11/1/2011'

;with cte as
(
  select [action],
         [date],
         row_number() over(order by [date], [action]) as rn
  from @T
)
select C1.[action],
       case when C2.[date] is null
         then C1.[date]
       end as [date]   
from cte as C1
  left outer join cte as C2
    on C1.rn = C2.rn + 1 and
       C1.[date] = C2.[date]
order by C1.rn