How to replace duplicates with a blank value?

1

I have a sql that pulls in duplicate amounts for a column, which is correct since there are duplicates. However, I would like to change those duplicates to blank.
results
On the attached the yellow highlighted amounts should show as blanks.
the last part of my sql looks like this and is where I need to replace the duplicates.

SELECT
aa.ParentOrganizationName as 'Provider'
,aa.CountyName AS 'County'

,af.Allocation

,Sum(aa.Jul) AS 'Jul'
,Sum(aa.Aug) AS 'Aug'
,Sum(aa.Sep) AS 'Sep'
,Sum(aa.Oct) AS 'Oct'
,Sum(aa.Nov) AS 'Nov'
,Sum(aa.Dec) AS 'Dec'
,Sum(aa.Jan) AS 'Jan'
,Sum(aa.Feb) AS 'Feb'
,Sum(aa.Mar) AS 'Mar'
,Sum(aa.Apr) AS 'Apr'
,Sum(aa.May) AS 'May'
,Sum(aa.Jun) AS 'Jun'

,SUM((aa.Jul)+(aa.Aug)+(aa.sep)+(aa.Oct)+(aa.Nov)+(aa.Dec)+(aa.Jan)+(aa.feb)+(aa.Mar)+(aa.apr)+(aa.may)+(aa.jun)) AS 'Total Exp'

,af.[Pending/Encumbered]

FROM #TableA AS aa
INNER JOIN #allocationfinal AS af
ON aa.ParentOrganizationName = af.Provider

GROUP BY
aa.ParentOrganizationName
,aa.CountyName
,af.Allocation
,af.[Pending/Encumbered]

2

You can use a row_number function .

update aa
set allocation = ''
from(
select row_number() over (partition by allocation, order by Provider,County) as rowId,*
from #myTable) aa
1 Like
3

hi

hope this helps

just a demo proof of concept

create sample data script

drop table if exists #Dups

create table #dups ( name varchar(10) , id int )

insert into #dups select 'AA',1
insert into #dups select 'AA',1
insert into #dups select 'AA',1
insert into #dups select 'BB',2
insert into #dups select 'CC',3
insert into #dups select 'DD',4
insert into #dups select 'DD',4

; with cte as ( select ROW_NUMBER() OVER(partition by name order by id )  as rn , * from #dups ) 
  select 
        name 
 	  , case when rn <> 1 then NULL else id end  
  from 
     cte

image
image521×605 6.71 KB

1 Like
4

hi

hope this helps

actual query

; with cte as 
(
SELECT
      row_number() Over(partition by aa.ParentOrganizationName order by af.Allocation ) as rn
    , aa.ParentOrganizationName as 'Provider'
    , aa.CountyName AS 'County'
    , af.Allocation
    , Sum(aa.Jul) AS 'Jul'
    , Sum(aa.Aug) AS 'Aug'
    , Sum(aa.Sep) AS 'Sep'
    , Sum(aa.Oct) AS 'Oct'
    , Sum(aa.Nov) AS 'Nov'
    , Sum(aa.Dec) AS 'Dec'
    , Sum(aa.Jan) AS 'Jan'
    , Sum(aa.Feb) AS 'Feb'
    , Sum(aa.Mar) AS 'Mar'
    , Sum(aa.Apr) AS 'Apr'
    , Sum(aa.May) AS 'May'
    , Sum(aa.Jun) AS 'Jun'
    , SUM((aa.Jul)+(aa.Aug)+(aa.sep)+(aa.Oct)+(aa.Nov)+(aa.Dec)+(aa.Jan)+(aa.feb)+(aa.Mar)+(aa.apr)+(aa.may)+(aa.jun)) AS 'Total Exp'
    , af.[Pending/Encumbered]
FROM 
    #TableA AS aa
        INNER JOIN 
    #allocationfinal AS af
        ON 
     	  aa.ParentOrganizationName = af.Provider
GROUP BY
          aa.ParentOrganizationName
       ,  aa.CountyName
       ,  af.Allocation
       ,  af.[Pending/Encumbered]
)	   
SELECT 
      Provider
    , County
    , case when rn <> 1 then NULL else Allocation end  as Allocation
    , Jul
    , Aug
    , Sep
    , Oct
    , Nov
    , Dec
    , Jan
    , Feb
    , Mar
    , Apr
    , May
    , Jun
    , Total Exp
    , [Pending/Encumbered]
FROM 
   cte
1 Like