SQL - Replace repeated rows with null values while

Question

I am trying to get only one instance of a year instead of 12 because I am using this column in a lookup table to provide parameters to a report. Because I am using both monthly and yearly data, I am trying to get them both in the same table.

I have a table like this

--Date--------Year
-------------------- 
1/2012-------2012 
2/2012-------2012 
3/2012-------2012 
4/2012-------2012 
5/2012-------2012 
6/2012-------2012 
7/2012-------2012 
8/2012-------2012 
9/2012-------2012 
10/2012------2012 
11/2012------2012 
12/2012------2012 
1/2013-------2013 
2/2013-------2013 

And this is my desired table

--Date--------Year 
-------------------- 
1/2012-------2012 
2/2012-------null 
3/2012-------null 
4/2012-------null 
5/2012-------null 
6/2012-------null 
7/2012-------null 
8/2012-------null 
9/2012-------null 
10/2012------null 
11/2012------null 
12/2012------null 
1/2013-------2013 
2/2013-------null 

Can someone give me an idea of how to solve a problem like this?

The code I am using right now is

SELECT     CAST(MONTH(rmp.EcoDate) AS Varchar(2)) + '/' + CAST(YEAR(rmp.EcoDate) AS varchar(4)) AS Date, Year(rmp.EcoDate) as EcoYear
FROM         PhdRpt.ReportCaseList_542 AS rcl INNER JOIN
                      CaseCases AS cc ON rcl.CaseCaseId = cc.CaseCaseId INNER JOIN
                      PhdRpt.RptMonthlyProduction_542 AS rmp ON rcl.ReportRunCaseId = rmp.ReportRunCaseId`
GROUP BY rmp.EcoDate

Answer 1

You can do this by enumerating the rows within a year. Then update all but the first:

with toupdate as (
      select t.*, row_number() over (partition by [year] order by [date]) as seqnum
      from t
     )
update toupdate
    set [year] = NULL
    where seqnum > 1;

If you want this as a select statement:

with ts as (
      select t.*, row_number() over (partition by [year] order by [date]) as seqnum
      from t
     )
select [date],
       (case when seqnum = 1 then [year] end) as [year]
from ts;