Conditional column based on unique values in another column

Question

I have a table like this.

ID	Classification_ID	Location
1001	10	A
1001	10	B
1002	NULL	A

Based on the classification_ID, I create Cat_1_Count and Cat_2_Count columns as:

SELECT ID, Location
CASE
    WHEN Classification_ID IS NOT NULL THEN 1 ELSE 0
END AS Cat_1_Count
CASE
    WHEN Classification_ID IS NULL THEN 1 ELSE 0
END AS Cat_2_Count
FROM Table1

However, In addition to the condition on Classification_ID, I want to have Cat_1_Count or Cat_2_Count columns to be set to 1, for unique ID numbers, regardless of the Location. So what I want is results like this,

ID	Classification_ID	Location	Cat_1_count	Cat_2_count
1001	10	A	1	0
1001	10	B	0	0
1002	NULL	A	0	1

Answer 1

Use row_number() to identify the row. Add the condition to the case expression.

SELECT ID, Location,
    CASE
      WHEN Classification_ID IS NOT NULL 
      AND  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Location) = 1
      THEN 1 
      ELSE 0
      END AS Cat_1_Count,
    CASE
      WHEN Classification_ID IS NULL 
      AND  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Location) = 1
      THEN 1 
      ELSE 0
      END AS Cat_2_Count
FROM Table1