Rolling up data with case expression
19:50 07 Mar 2022

I'm using a case expression but getting multiple values filled with NULLs. How would I roll it up?

To create the table:

CREATE TABLE MedQuantity (
    PatName varchar(1),
    Contact# int,
    MedID int,
    MedQty int,
    EntryDate date);    

INSERT INTO MedQuantity(PatName, Contact#,MedID,MedQty, EntryDate)
VALUES ('RODI, FEZ', 7, NULL, NULL, '2021-07-01'),
('RODI, FEZ',5, 32324, 185, NULL),
('RODI, FEZ',5, 4081, 15, NULL),
('RODI, FEZ',5, 4081, 50, NULL)

Which gives this output:

Name       Contact# MedID   MedQty  EntryDate
RODI, FEZ   7       NULL    NULL    2021-07-01 08:40:00
RODI, FEZ   5       32324   185     NULL
RODI, FEZ   5       4081    15      NULL
RODI, FEZ   5       4081    50      NULL

Below is my query:

SELECT
    [PatName]
    , [Medication ID] = MAX(CASE WHEN T1.[Contact#] = 5 THEN T1.[MedID] END)
    , [Medication Qty] = MAX(CASE WHEN T1.[Contact#] = 5 THEN T1.[MedQty] END)
    , [Entry Date] = MAX(CASE WHEN T1.[Contact#] = 7 THEN T1.[EntryDate] END)
FROM MedQuantity T1
GROUP By T1.[PatName]

Which gives this result:

Name         Medication ID  Medication Qty  Entry Date
RODI, FEZ   4081             50              2021-07-01 08:40:00

However, I'm expecting:

enter image description here

sql sql-server