SQL case with partition by and select rows with specific value

Question

I have a scenario in which I have to use a case condition in which if the value matches then I need to get value from a table otherwise I need to hard code it to 1 and 0 .

Table License:

Number   permit   reviewpermit   State
---------------------------------------
101       0        1              TX
101       1        0              OK
101       1        1              NC
102       1        1              LA
102       0        1              OK

Condition :

1. If any state is 'TX' for a 'Number' group, then select 'permit', 'reviewpermit' values from the TX state for all other states for that 'Number' group.
2. If it's some other state for a 'Number' group then select '1' as 'permit' and '0' as 'reviewpermit' for all states for that 'Number' group.

Desired output:

Number   permit   reviewpermit   State
--------------------------------------
101       0        1              TX
101       0        1              OK
101       0        1              NC
102       1        0              LA     
102       1        0              OK

The 101 group has all permit and reviewpermit values according to Texas state The 102 group has all permit and reviewpermit values '1' and '0' respectively.

This is the code I have written so far:

 SELECT
     Number,
     MAX(CASE WHEN State = 'TX' THEN Permit ELSE 1 END) OVER (PARTITION BY [Number]) AS permit, 
     MAX(CASE WHEN State = 'TX' THEN ReviewPermit ELSE 0 END) OVER (PARTITION BY [Number]) AS reviewpermit, 
     state
 FROM 
     License

But it doesn't return the desired output.

Answer 1

I think this logic does what you want:

select Number,
       coalesce(MAX(CASE WHEN State = 'TX'  THEN Permit END) OVER (PARTITION BY [Number]),
                1) as permit, 
       coalesce(MAX(CASE WHEN State = 'TX' THEN ReviewPermit END) OVER (PARTITION BY [Number]),
                0) AS reviewpermit, state
from License;

This checks for "Texas" in the MAX(). If it is not there, the value is NULL, so the COALESCE() picks up the default value.