First, I find it a lot easier to read if you use the Table Value Constructor

insert into @DedTest

VALUES

('California', '56756', '1003375', 80915, 1, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375', 80915, 2, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375', 80915, 3, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375', 80915, 4, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375', 80915, 5, cast(2000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375', 80916, 6, cast(5000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375', 80916, 7, cast(5000000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375', 80921, 1, cast(100000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375', 80922, 2, cast(500000.00 as decimal(18,2)), cast(0 as decimal(18,2))),

('California', '56756', '1003375', 80924, 2, cast(0 as decimal(18,2)), cast(500000 as decimal(18,2))),

('California', '56756', '1003375', 80924, 3, cast(0 as decimal(18,2)), cast(500000 as decimal(18,2))),

('California', '56756', '1003375', 80924, 4, cast(0 as decimal(18,2)), cast(500000 as decimal(18,2))

)

If your descriptions of your groups are completely accurate, then you could use a simple CASE statement. (I've put it in a CROSS APPLY so that I could give it an alias).

SELECT dt.Area, dt.AccountNum, dt.PolicyNum, g.GroupNm, MAX(dt.LocDedAmt) AS LocDedAmt, MAX(dt.NonLocDedAmt) AS NonLocDedAMt

FROM @DedTest dt

CROSS APPLY(

VALUES

(CASE

WHEN dt.LocID = 1 THEN 'A'

WHEN dt.LocID = 2 THEN 'B'

WHEN dt.LocID = 3 THEN 'D'

WHEN dt.LocID = 4 THEN 'D'

WHEN dt.LocID = 5 THEN 'E'

WHEN dt.LocID = 6 THEN 'C'

WHEN dt.LocID = 7 THEN 'C'

END)

) g(GroupNm)

GROUP BY dt.Area, dt.AccountNum, dt.PolicyNum, g.GroupNm

Drew

PS: When setting up sample data, it helps to have more variety. Four of your five groups have the exact same LocDedAmt for the final result.

PPS: When providing results, it helps if they match the sample data. The entries for both LocIDs 6 and 7 have 5000000, but the desired results lists 2000000.