CODEWITHSUNDEEP
sqlnullcaserdbms
Raghu
Raghu

Reputation: 27

Avoid extra NULL rows from case statement in new columns

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

Upvotes: 0

Views: 636

Answers (1)

ahmed
ahmed

Reputation: 9181

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.

Upvotes: 2

Related Questions