Is there a loop function which can automatize this?

Question

I have these lines of code which you can find below and I want to automatize this (most probably using a loop function) by just giving the input: 'CITY', 'COUNTY', 'PARTNERS', 'PRODUCT'. Is there any way to do this? Also the names of temporary tables ('CUSTOMERS_MND_0', CUSTOMERS_MND_1', 'CUSTOMERS_MND_2', 'CUSTOMERS_MND_3', 'CUSTOMERS_MND_4') are not important, so those can be renamed as the variable names.

The issue I'm facing is that "UNIQUE_KEY" is not unique and I need to have it unique in the final db. Please find an example.

Initial DB:

UNIQUE_KEY	CITY	COUNTY	PARTNERS	PRODUCT
111	VIENNA	A	W	1
111	NAPLES	B	X	7
112	VIENNA	B	Y	3
113	NAPLES	B	W	4
113	NAPLES	A	W	4
114	VIENNA	A	W	1
115	VIENNA	B	W	4
115	NAPLES	A	W	4
115	VIENNA	B	X	7
115	VIENNA	B	Y	3
116	NAPLES	B	W	4
116	NAPLES	A	W	4
116	VIENNA	A	W	1

FINAL DB:

UNIQUE_KEY	CITY	COUNTY	PARTNERS	PRODUCT
111	VIENNA	A	W	1
112	VIENNA	B	Y	3
113	NAPLES	B	W	4
114	VIENNA	A	W	1
115	VIENNA	B	Y	3
116	VIENNA	A	W	1

SELECT AA.*
INTO #CUSTOMERS_MND_1
FROM #CUSTOMERS_MND_0 AA
     INNER JOIN (SELECT UNIQUE_KEY,
                        MAX(CITY) AS MAXCITY
                 FROM #CUSTOMERS_MND_0
                 GROUP BY UNIQUE_KEY) BB ON AA.UNIQUE_KEY = BB.UNIQUE_KEY
                                        AND AA.CITY = BB.MAXCITY;
DROP TABLE #CUSTOMERS_MND_0;

SELECT AA.*
INTO #CUSTOMERS_MND_2
FROM #CUSTOMERS_MND_1 AA
     INNER JOIN (SELECT UNIQUE_KEY,
                        MAX(COUNTY) AS MAXCOUNTY
                 FROM #CUSTOMERS_MND_1
                 GROUP BY UNIQUE_KEY) BB ON AA.UNIQUE_KEY = BB.UNIQUE_KEY
                                        AND AA.COUNTY = BB.MAXCOUNTY;
DROP TABLE #CUSTOMERS_MND_1;

SELECT AA.*
INTO #CUSTOMERS_MND_3
FROM #CUSTOMERS_MND_2 AA
     INNER JOIN (SELECT UNIQUE_KEY,
                        MAX(PARTNERS) AS MAXPARTNERS
                 FROM #CUSTOMERS_MND_2
                 GROUP BY UNIQUE_KEY) BB ON AA.UNIQUE_KEY = BB.UNIQUE_KEY
                                        AND (AA.PARTNERS = BB.MAXPARTNERS
                                          OR AA.PARTNERS IS NULL
                                         AND BB.MAXPARTNERS IS NULL);
DROP TABLE #CUSTOMERS_MND_2;

SELECT AA.*
INTO #CUSTOMERS_MND_4
FROM #CUSTOMERS_MND_3 AA
     INNER JOIN (SELECT UNIQUE_KEY,
                        MAX(PRODUCT) AS MAXPRODUCT
                 FROM #CUSTOMERS_MND_3
                 GROUP BY UNIQUE_KEY) BB ON AA.UNIQUE_KEY = BB.UNIQUE_KEY
                                        AND (AA.PRODUCT = BB.MAXPRODUCT
                                          OR AA.PRODUCT IS NULL
                                         AND BB.MAXPRODUCT IS NULL);
DROP TABLE #CUSTOMERS_MND_3;

It works, but for more joins it is not time efficient.

Answer 1

my guess is you want the max city per unique key, within that city the max county, within that county the max partners and within that partner the max product in which case generating a row number and selecting row number 1 gives the same result as your code

create table #CUSTOMERS_MND_0
(unique_key int,city varchar(10),county varchar(10),partners varchar(10),product int);
go

insert into #CUSTOMERS_MND_0 values
(111,'vienna','a','w',7),(111,'naples','b','x',7),(111,'vienna','b','w',6),(111,'vienna','b','x',5)
go
 
with cte as
(select *, row_number() over (partition by unique_key order by city desc , county desc, partners desc,product desc) rn 
from
#CUSTOMERS_MND_0
)
select * from cte where rn = 1;

your code plus

select * from #CUSTOMERS_MND_3

unique_key  city       county     partners   product
----------- ---------- ---------- ---------- -----------
111         vienna     b          x          5

my code

unique_key  city       county     partners   product     rn
----------- ---------- ---------- ---------- ----------- --------------------
111         vienna     b          x          5           1

Now we have simplified code we can think about automation. You say you want to supply known columns as part of this process but unique key will always be required and source tables is known so--see https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=355f06fb7d51ba8fed9ea4e6d1e23d07 for an example of how using a temp table to hold the columns and the priority order in this case by identity. Then read up on dynamic sql..