Counting entries of a unique entry of another field and concatenate the result with the unique field in a new column

Question

I want to create a query that will search the table and find the following:

If a POL_N entry has ENDO_N entries then the COUNT ENDO_N field will start counting the ENDO_N entries and will get the assigned name as shown below.

For example POL002 has two ENDO_N entries, hence the COUNT ENDO_N will be POL002-1 and POL002-2.

Do you have any suggestions?

Answer 1

I recreated the table specified with the next T-SQL code:

create table StackOverflowTest(POL_N nvarchar(100),ENDO_N nvarchar(100));
insert into StackOverflowTest(POL_N,ENDO_N)
values('POL001','0'),
      ('POL002','0'),
      ('POL002','ENDO001'),
      ('POL002','ENDO002'),
      ('POL003','ENDO003'),
      ('POL003','ENDO004');

T-SQL for getting the column specified. I would be wary of doing this on large datasets though, this is the unoptimized first draft:

  select POL_N,
         ENDO_N,
         case when ENDO_N = cast(0 as nvarchar(2)) 
         then cast(0 as nvarchar(2)) 
         else  POL_N +'-'+ cast(ROW_NUMBER() OVER (PARTITION BY POL_N ORDER BY ENDO_N desc) as nvarchar(100)) 
         end as COUNT_ENDO_N
    from StackOverflowTest
    order by POL_N, COUNT_ENDO_N;