Assigning same status same row group

Question

Hi there, just need to assing always the same value (1) regardless if it has 0 or NULL.

DECLARE @T1 AS TABLE(ID2 SMALLINT, BUILDING INT, BUILDING_STATUS BIT)

INSERT INTO @T1(ID2,BUILDING, BUILDING_STATUS)
VALUES(1,5671,0),(2,5671,NULL), (3,5671,1),(4,5673,1),(5,5674,2)

SELECT * FROM @T1

Output should be:

1 5671 1
2 5671 1
3 5671 1
4 5673 1

Thanks a lot,

Answer 1

Try another query:

SELECT ID2, BUILDING, max(cast(isnull(BUILDING_STATUS, 1) as int)) over (partition by BUILDING) as BUILDING_STATUS
FROM @T1
order by ID2

Although, it is not clear what to display if all values in a group are NULL.

Answer 2

Try a query:

SELECT ID2, BUILDING, 1 as BUILDING_STATUS
FROM @T1

(Note that you cannot store 2 to BIT).