Assign value based on table and ID I wish to assign a value in my row which should be based on the country as: 


Only US: Should be assigned USA 
only Other countries: Should be
    assigned International 
US + Other countries: Should be assigned
    Global


So for the below table:

    DECLARE @A TABLE
    (
    PolicyID int,
    PolicylocationID int,
    Country VARCHAR(100)
    )
    INSERT INTO @A
    (
    PolicyID,PolicylocationID,Country
    )
    VALUES
    (1,1,'US'),
    (1,2,'US'),
    (1,3,'US'),
    (1,4,'US'),
    (2,1,'NA'),
    (2,2,'PA'),
    (2,3,'CA'),
    (2,4,'ZA'),
    (3,1,'US'),
    (3,2,'NA'),
    --(3,3,'PA'),
    (4,1,'US'),
    --(4,2,'US'),
    (4,3,'US'),
    (5,1,'US'),
    --(5,2,'NA'),
    (5,3,'PA'),
    (6,1,'NA'),
    --(6,2,'PA'),
    (6,3,'CA') 

    Select * into #temp
    from @A
Select * from #temp


the output should be:

DECLARE @A TABLE
(
PolicyID int,
PolicylocationID int,
Country VARCHAR(100),
CType VARCHAR(100)
)
INSERT INTO @A
(
PolicyID,PolicylocationID,Country,CType
)
VALUES
(1,1,'US','US'),
(1,2,'US','US'),
(1,3,'US','US'),
(1,4,'US','US'),
(2,1,'NA','Internationl'),
(2,2,'PA','Internationl'),
(2,3,'CA','Internationl'),
(2,4,'ZA','Internationl'),
(3,1,'US','Global'),
(3,2,'NA','Global'),
--(3,3,'PA','Global'),
(4,1,'US','US'),
--(4,2,'US','US'),
(4,3,'US','US'),
(5,1,'US','Global'),
--(5,2,'NA','Global'),
(5,3,'PA','Global'),
(6,1,'NA','Internationl'),
--(6,2,'PA','Internationl'),
(6,3,'CA','Internationl') 

SElect * into #temp
from @A 


Any ideas on how to achieve this?
WITH cte AS ( SELECT PolicyID,
                     PolicylocationID,
                     Country,
                     COUNT(CASE WHEN Country = 'US' THEN 1 END) OVER (PARTITION BY PolicyID) us,
                     COUNT(Country) OVER (PARTITION BY PolicyID) total
              FROM test
            )
SELECT PolicyID,
       PolicylocationID,
       Country,
       CASE WHEN us = total THEN 'US'
            WHEN us = 0     THEN 'International'
            ELSE 'Global'
       END CType 
FROM cte


fiddle