SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employment_by_industry (
    objectid int,
    agr_forest_fish int,
    mining_quarry int,
    mfg int,
    electric int,
    water_sew int
);
INSERT INTO employment_by_industry (objectid, agr_forest_fish, mining_quarry, mfg, electric, water_sew)
VALUES (1, 20797, 428, 955, 30, 70);

INSERT INTO employment_by_industry (objectid, agr_forest_fish, mining_quarry, mfg, electric, water_sew)
VALUES (2, 6792, 6868, 8121, 238, 300);

INSERT INTO employment_by_industry (objectid, agr_forest_fish, mining_quarry, mfg, electric, water_sew)
VALUES (3, 8728, 2405, 1544, 43, 121);
3 rows affected

select * from employment_by_industry


objectid
agr_forest_fish
mining_quarry
mfg
electric
water_sew


1
20797
428
955
30
70

2
6792
6868
8121
238
300

3
8728
2405
1544
43
121


with temp as (
   select objectid, industry, count
   from employment_by_industry
   unpivot
   (
     count
     for industry in (agr_forest_fish, mining_quarry, mfg, electric, water_sew)
   ) u
)
select distinct t1.objectid, t2.industry
from temp t1
cross apply (
    select top 1 objectid, industry
    from temp
    where t1.objectid = objectid
order by count desc) t2


objectid
industry


1
agr_forest_fish

2
mfg

3
agr_forest_fish