Show empty data in similar data in columns in sql

Question

I have table in which i have data like this , in which firstcol has data which can be repeated

with myTable ( firstcol,secondcol,thirdcol,fourthCol) as 
(  
    select  'sameText1' ,'anytext','anytext1','anytext2' union all
    select  'sameText1' ,'anytext','anytext1','anytext2' union all
    select  'sameText2' ,'anytext','anytext1','anytext2' union all
    select  'sameText2' ,'anytext','anytext1','anytext2' union all
    select  'sameText3' ,'anytext','anytext1','anytext2' union all
    select  'sameText3' ,'anytext','anytext1','anytext2' union all
    select  'sameText3' ,'anytext','anytext1','anytext2' union all
    select  'sameText3' ,'anytext','anytext1','anytext2'
)
select  firstcol,secondcol,thirdcol,fourthCol  from myTable

now my requirement is to show the data which is similar like this

with myDesiredTable ( firstcol,secondcol,thirdcol,fourthCol) as 
(  
    select  'sameText1' ,'anytext','anytext1','anytext2' union all
    select  '' ,'anytext','anytext1','anytext2' union all
    select  'sameText2' ,'anytext','anytext1','anytext2' union all
    select  '' ,'anytext','anytext1','anytext2' union all
    select  'sameText3' ,'anytext','anytext1','anytext2' union all
    select  '' ,'anytext','anytext1','anytext2' union all
    select  '' ,'anytext','anytext1','anytext2' union all
    select  '' ,'anytext','anytext1','anytext2'
)
select  firstcol,secondcol,thirdcol,fourthCol  from myDesiredTable 

to show the empty instead of the similar string not sure how can i do it in SQL

Answer 1

Try using ROW_NUMBER to achieve this:

;WITH CTE_result
AS
( 
   SELECT firstcol, secondcol, thirdcol, fourthcol,
     ROW_NUMBER() OVER (PARTITION BY firstcol ORDER BY firstcol) rownum
  FROM my_table
)
SELECT
  CASE rownum
    WHEN 1 THEN firstcol
    ELSE ''
  END AS firstcol,
  secondcol,
  thirdcol,
  fourthcol
FROM CTE_result;

Since we are partitioning it by firstcol, we will have 1 for first occurrence and >1 for subsequent occurrence. Hence we use CASE WHEN and use ROWNUM to get desired result.

db<>fiddle link here