NULLS being removed in unpivot-CodePudding

I currently have the following code

with physician_diag as (

   SELECT baseentityid, eventdate,locationid,teamid,average_muac,child_age,child_gender,physician_diagnosis, dignosis_data
   FROM [VITAL_DWH].[vr].[event_physician_visit]   
UNPIVOT  
   (dignosis_data FOR physician_diagnosis  IN   
      (
     well_baby,
severe_pneumonia
)  
)

AS unpvt 
),

final as (
-- Final
select *, dense_rank() OVER (PARTITION BY baseentityid ORDER BY eventdate) AS rn from (
select * from physician_diag  
 
 ) F

The problem is that NULLS are being removed, so I am missing out on some of the original rows. Any idea how to fix this? I have read that cross join lateral is a possible way, but I could not get a grip on how to implement that. Please help.

CodePudding user response：

UNPIVOT removes nulls. You can instead use CROSS APPLY (VALUES to unpivot, this is in any case much more flexible.

with physician_diag as (
    SELECT
      baseentityid,
      eventdate,
      locationid,
      teamid,
      average_muac,
      child_age,
      child_gender,
      physician_diagnosis,
      dignosis_data
    FROM vr.event_physician_visit epv
    CROSS APPLY (VALUES
        ('well_baby', epv.well_baby),
        ('severe_pneumonia', epv.severe_pneumonia)
    ) v(physician_diagnosis, dignosis_data)
),
final as (
    select *,
      dense_rank() OVER (PARTITION BY baseentityid ORDER BY eventdate) AS rn
    from physician_diag  
)
.....

I note that you could possibly put the DENSE_RANK before unpivoting, which may allow it to hit an index. The results should be the same in this case.

with ranked as (
    select *,
      dense_rank() OVER (PARTITION BY epv.baseentityid ORDER BY epv.eventdate) AS rn
    from vr.event_physician_visit epv
),
final as (
    SELECT
      baseentityid,
      eventdate,
      locationid,
      teamid,
      average_muac,
      child_age,
      child_gender,
      physician_diagnosis,
      dignosis_data
    FROM ranked epv
    CROSS APPLY (VALUES
        ('well_baby', epv.well_baby),
        ('severe_pneumonia', epv.severe_pneumonia)
    ) v(physician_diagnosis, dignosis_data)
)
.....