Avoid extra NULL rows from case statement in new columns

Question

Below is the expected result and we want to avoid the nulls from actual result from query

CREATE OR REPLACE TABLE STAGING.Test1  (
    TYPE_ID INTEGER,
    TYPE INTEGER,
    Value Varchar(100)
)

INSERT INTO STAGING.Test1 VALUES
(1,100,'complete'),
(1,200,'2022/12/12'),
(1,300,'Y'),
(2,100,'pending'),
(2,200,'2021/11/11'),
(2,300,'N')


SELECT TYPE_ID
,CASE WHEN TYPE = 100 Then Value END AS STATUS 
,CASE WHEN TYPE = 200 Then Value END AS DATE 
,CASE WHEN TYPE = 300 Then Value END AS FLAg 
 FROM STAGING.Test1 

        

Actual Result

TYPE_ID	STATUS	DATE	FLAG
1	complete	null	null
1	null	12/12/2022	null
1	null	null	Y
2	pending	null	null
2	null	11/11/2021	null
2	null	null	N

Expected result

TYPE_ID	STATUS	DATE	FLAG
1	complete	12/12/2022	Y
2	pending	11/11/2021	N

result image

Answer 1

You may use conditional aggregation as the following:

SELECT TYPE_ID
,MAX(CASE WHEN TYPE = 100 Then Value END) AS STATUS 
,MAX(CASE WHEN TYPE = 200 Then Value END) AS DATE 
,MAX(CASE WHEN TYPE = 300 Then Value END) AS FLAg 
 FROM Test1
GROUP BY  TYPE_ID

See a demo on SQL Server.